The following code shows how to reference data in rows other than the current row.
SELECT empno , begindate , enddate , msal , LAG(msal) OVER (PARTITION BY empno ORDER BY begindate) prev_sal FROM history ORDER BY empno, begindate;
Here is an example of using the LAG function to calculate the raise someone received.
SQL> SQL>-- from w ww .ja va2s. c o m SQL> drop table history; Table dropped. SQL> alter session set NLS_DATE_FORMAT='DD-MM-YYYY'; Session altered. SQL> create table history( 2 empno NUMBER(4) , 3 beginyear NUMBER(4) , 4 begindate DATE , 5 enddate DATE , 6 deptno NUMBER(2) , 7 msal NUMBER(6,2), 8 comments VARCHAR2(60)) ; SQL> SQL> insert into history values (7001,2000,'01-01-2000','01-02-2000',40, 950,''); SQL> insert into history values (7370,2000,'01-02-2000', NULL ,20, 800,''); SQL> insert into history values (7002,1988,'01-06-1988','01-07-1989',30,1000,''); SQL> insert into history values (7500,1989,'01-07-1989','01-12-1993',30,1300,''); SQL> insert into history values (7501,1993,'01-12-1993','01-10-1995',30,1500,''); SQL> insert into history values (7401,1995,'01-10-1995','01-11-1999',30,1700,''); SQL> SQL> SQL> SELECT empno 2 , begindate 3 , enddate 4 , msal 5 , LAG(msal) OVER (PARTITION BY empno 6 ORDER BY begindate) prev_sal 7 FROM history 8 ORDER BY empno, begindate; EMPNO | BEGINDATE | ENDDATE | MSAL | PREV_SAL --------- | ---------- | ---------- | --------- | --------- 07001.00 | 01-01-2000 | 01-02-2000 | 00950.00 | [N/A] 07002.00 | 01-06-1988 | 01-07-1989 | 01000.00 | [N/A] 07370.00 | 01-02-2000 | [N/A] | 00800.00 | [N/A] 07401.00 | 01-10-1995 | 01-11-1999 | 01700.00 | [N/A] 07500.00 | 01-07-1989 | 01-12-1993 | 01300.00 | [N/A] 07501.00 | 01-12-1993 | 01-10-1995 | 01500.00 | [N/A] 6 rows selected. SQL> SQL>