8i | 9i | 10g | 11g | 12c | Misc | PL/SQL | SQL | RAC | Linux

Home » Articles » Misc » Here

# LAG and LEAD Analytic Functions

The LAG and LEAD analytic functions were introduced in 8.1.6 to give access to multiple rows within a table, without the need for a self-join.

Related articles.

## Introduction

Both LAG and LEAD functions have the same usage, as shown below.

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
• value_expression - Can be a column or a built-in function, except for other analytic functions.
• offset - The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.
• default - The value returned if the offset is outside the scope of the window. The default value is NULL.

Looking at the EMP table, we query the data in salary (SAL) order.

SELECT empno,
ename,
job,
sal
FROM   emp
ORDER BY sal;

EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
7369 SMITH      CLERK            800
7900 JAMES      CLERK            950
7521 WARD       SALESMAN        1250
7654 MARTIN     SALESMAN        1250
7934 MILLER     CLERK           1300
7844 TURNER     SALESMAN        1500
7499 ALLEN      SALESMAN        1600
7782 CLARK      MANAGER         2450
7698 BLAKE      MANAGER         2850
7566 JONES      MANAGER         2975
7788 SCOTT      ANALYST         3000
7902 FORD       ANALYST         3000
7839 KING       PRESIDENT       5000

SQL>

## LAG

The LAG function is used to access data from a previous row. The following query returns the salary from the previous row to calculate the difference between the salary of the current row and that of the previous row. Notice that the ORDER BY of the LAG function is used to order the data by salary.

SELECT empno,
ename,
job,
sal,
LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM   emp;

EMPNO ENAME      JOB              SAL   SAL_PREV   SAL_DIFF
---------- ---------- --------- ---------- ---------- ----------
7369 SMITH      CLERK            800          0        800
7900 JAMES      CLERK            950        800        150
7876 ADAMS      CLERK           1100        950        150
7521 WARD       SALESMAN        1250       1100        150
7654 MARTIN     SALESMAN        1250       1250          0
7934 MILLER     CLERK           1300       1250         50
7844 TURNER     SALESMAN        1500       1300        200
7499 ALLEN      SALESMAN        1600       1500        100
7782 CLARK      MANAGER         2450       1600        850
7698 BLAKE      MANAGER         2850       2450        400
7566 JONES      MANAGER         2975       2850        125
7788 SCOTT      ANALYST         3000       2975         25
7902 FORD       ANALYST         3000       3000          0
7839 KING       PRESIDENT       5000       3000       2000

SQL>

The LEAD function is used to return data from the next row. The following query returns the salary from the next row to calulate the difference between the salary of the current row and the following row.

SELECT empno,
ename,
job,
sal,
LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
FROM   emp;

EMPNO ENAME      JOB              SAL   SAL_NEXT   SAL_DIFF
---------- ---------- --------- ---------- ---------- ----------
7369 SMITH      CLERK            800        950        150
7900 JAMES      CLERK            950       1100        150
7876 ADAMS      CLERK           1100       1250        150
7521 WARD       SALESMAN        1250       1250          0
7654 MARTIN     SALESMAN        1250       1300         50
7934 MILLER     CLERK           1300       1500        200
7844 TURNER     SALESMAN        1500       1600        100
7499 ALLEN      SALESMAN        1600       2450        850
7782 CLARK      MANAGER         2450       2850        400
7698 BLAKE      MANAGER         2850       2975        125
7566 JONES      MANAGER         2975       3000         25
7788 SCOTT      ANALYST         3000       3000          0
7902 FORD       ANALYST         3000       5000       2000
7839 KING       PRESIDENT       5000          0      -5000

SQL>