Lag salary over, lead salary over

  
SQL>
SQL> create table history
  2  ( empno      NUMBER(4)
  3  , beginyear  NUMBER(4)
  4  , begindate  DATE
  5  , enddate    DATE
  6  , deptno     NUMBER(2)
  7  , sal        NUMBER(6,2)
  8  , comments   VARCHAR2(60)
  9  , constraint H_PK         primary key          (empno,begindate)
 10  , constraint H_BEG_END    check                (begindate < enddate)
 11  ) ;

Table created.

SQL>
SQL>
SQL> alter session  set NLS_DATE_FORMAT='DD-MM-YYYY';

Session altered.

SQL>
SQL> insert into history values (1,2000,'01-01-2000','01-02-2000',40, 950,'');

1 row created.

SQL> insert into history values (1,2000,'01-02-2000', NULL       ,20, 800,'restarted');

1 row created.

SQL> insert into history values (2,2009,'01-11-2009', NULL       ,30,1600,'just hired');

1 row created.

SQL>
SQL>
SQL> select empno, begindate, sal
  2  ,      LAG(sal) over
  3         ( partition by empno
  4           order by empno, begindate
  5         ) as prev_sal
  6  ,      LEAD(sal) over
  7         ( partition by empno
  8           order by empno, begindate
  9         ) as next_sal
 10  from   history
 11  order  by empno, begindate;

     EMPNO BEGINDATE         SAL   PREV_SAL   NEXT_SAL
---------- ---------- ---------- ---------- ----------
         1 01-01-2000        950                   800
         1 01-02-2000        800        950
         2 01-11-2009       1600

SQL>
SQL> drop table history;

Table dropped.

SQL>

Related examples in the same category

1.	Include a final ordering of the result set with an ORDER BY at the end of the query
2.	sum over (nothing)
3.	sum over partition by, order by
4.	Get Products: RANK() OVER in subquery
5.	Using Analytic Functions AVG(Mark) OVER (PARTITION BY StudentID ORDER BY StudentID, Mark)
6.	Using Analytic Functions: AVG(Mark) OVER
7.	Using Analytic Functions: AVG(Mark) OVER (ORDER BY StudentID, Mark)
8.	Using Analytic Functions: AVG(Mark) OVER(PARTITION BY StudentID ORDER BY StudentID, Mark RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
9.	Using Analytic Functions: AVG(Mark) OVER(PARTITION BY StudentID ORDER BY StudentID, Mark ROWS 1 preceding
10.	Use over partition in subquery

Lag salary over, lead salary over : Over « Analytical Functions « Oracle PL / SQL

Related examples in the same category