Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Oracle: Previous, current and next values (Read 30083 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Oracle: Previous, current and next values
07.11.07 at 22:34:28
 
Oracle: Previous, current and next values
 
The Oracle lag and lead functions can be used to retrieve values from a previous row (lag) or a next row (lead). Consider the following example.
 
 
SQL> select deptno
  2  ,      lag(deptno) over (order by deptno) as previous
  3  ,      lead(deptno) over (order by deptno) as next
  4  from   scott.dept
  5  
SQL> /
 
DEPTNO   PREVIOUS       NEXT
------ ---------- ----------
    10                    20
    20         10         30
    30         20         40
    40         30  
     
In some cases the next and previous need to be determined per group of values. In the following example I grouped the rows by department number. I want to know the current, previous and next employee number per department.
     
SQL> select empno
  2  ,      deptno
  3  ,      lag(empno) over (order by empno) as previous
  4  ,      lead(empno) over (order by empno) as next
  5  from   scott.emp
  6  
SQL> /
 
EMPNO DEPTNO   PREVIOUS       NEXT
----- ------ ---------- ----------
 7369     20                  7499
 7499     30       7369       7521
 7521     30       7499       7566
 7566     20       7521       7654
 7654     30       7566       7698
 7698     30       7654       7782
 7782     10       7698       7788
 7788     20       7782       7839
 7839     10       7788       7844
 7844     30       7839       7876
 7876     20       7844       7900
 7900     30       7876       7902
 7902     20       7900       7934
 7934     10       7902  
 
14 rows selected
 
Clearly this doesn't do what I want. I need to group the results by deptno. To do this I added a partition by deptno to the over clause. Consider the following SQL that illustrated this.
 
SQL>      
 
 
SQL> select empno
  2  ,      deptno
  3  ,      lag(empno) over (partition by deptno order by empno) as previous
  4  ,      lead(empno) over (partition by deptno order by empno) as next
  5  from   scott.emp
  6  
SQL> /
 
EMPNO DEPTNO   PREVIOUS       NEXT
----- ------ ---------- ----------
 7782     10                  7839
 7839     10       7782       7934
 7934     10       7839  
 7369     20                  7566
 7566     20       7369       7788
 7788     20       7566       7876
 7876     20       7788       7902
 7902     20       7876  
 7499     30                  7521
 7521     30       7499       7654
 7654     30       7521       7698
 7698     30       7654       7844
 7844     30       7698       7900
 7900     30       7844  
 
14 rows selected
 
SQL>
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle: Example: partition by
Reply #1 - 01.04.09 at 09:13:38
 
Oracle: Example: partition by
 
A simple partition by SQL example. For each order row show how may other orders there are with the same amount.
 
select order.* , count(1) over (partition by amount) from orders
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1