This Oracle tutorial explains how to use the Oracle/PLSQL LAG function.
The Oracle/PLSQL LAG function queries more than one row in a table at a time without having to join the table to itself.
It returns values from a previous row in the table. To return a value from the next row, try using the LEAD function.
The syntax for the Oracle/PLSQL LAG function is:
LAG ( expression [, offset [, default] ] )
over ( [ query_partition_clause ] order_by_clause )
expression can contain other built-in functions, but can not contain any analytic functions.
offset is optional. It is the physical offset from the current row in the table. The default is 1.
default is optional. It is the value that is returned if the offset goes out of the bounds of the table. The default is null.
LAG(SUM(sal), 1) gets the previous row's sum of the salary.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- www. j a v a 2 s .c o m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> SELECT deptno, SUM(sal) AS dept_amount,
2 LAG(SUM(sal), 1) OVER (ORDER BY deptno) AS previous_dept_amount
3 FROM emp
4 GROUP BY deptno
5 ORDER BY deptno;
DEPTNO DEPT_AMOUNT PREVIOUS_DEPT_AMOUNT
---------- ----------- --------------------
10 5850
20 8275 5850
30 8450 8275
SQL>