LAG function used to find previous row value within the same table. It is an example of analytical function.
Syntax :-
LAG(column, context, default) over partition clause
context – number of rows to go backward. Default is 1
default – default value returns. Default is null
Examples :-
Consider the emp table and below rows
EMPNO | EMPNAME | SALARY |
20 | Samuel | 6900 |
21 | Mary | 7500 |
22 | Daniel | 6500 |
23 | Ricardo | 7800 |
24 | Mark | 7200 |
SQL > select empno,salary,lag(salary,1,0) over ( order by salary) as lag_salary
from emp;
EMPNO | EMPNAME | LAG_SALARY |
22 | 6500 | 0 |
20 | 6900 | 6500 |
24 | 7200 | 6900 |
21 | 7500 | 7200 |
23 | 7800 | 7500 |
For each row LAG function tries to find the previous row value.
lag(salary,2,0) over ( order by salary) will find the value prior 2 rows.
SQL > select empno,salary,lag(salary,2,0) over ( order by salary) as lag_salary
from emp ;
EMPNO | EMPNAME | LEAD_SALARY |
22 | 6500 | 0 |
20 | 6900 | 0 |
24 | 7200 | 6500 |
21 | 7500 | 6900 |
23 | 7800 | 7200 |
Also read about LEAD function.