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 function explanation
LAG function explanation

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s