This Oracle tutorial explains how to use the Oracle/PLSQL LEAD function.
The Oracle/PLSQL LEAD function can query more than one row in a table without having to join the table to itself.
It returns values from the next row in the table. To return a value from a previous row, try using the LAG function.
The syntax for the Oracle/PLSQL LEAD function is:
LEAD ( 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.
LEAD(SUM(sal),1) gets the next 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));
-- w w w . j a va2 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 LEAD(SUM(sal), 1) OVER (ORDER BY deptno) AS next_dept_amount
3 FROM emp
4 GROUP BY deptno
5 ORDER BY deptno;
DEPTNO DEPT_AMOUNT NEXT_DEPT_AMOUNT
---------- ----------- ----------------
10 5850 8275
20 8275 8450
30 8450
SQL>