LAG function Oracle

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.