LEAD function Oracle

LEAD function used to find next row value within the same table. It is an example of analytical function.

Syntax :-

LEAD(column, context, default) over partition clause

context – number of rows to go forward. 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,lead(salary,1,0) over ( order by salary) as lead_salary
from emp;

EMPNO SALARY LEAD_SALARY
22 6500 6900
20 6900 7200
24 7200 7500
21 7500 7800
23 7800 0

For each row LEAD function tries to find the next row value.

LEAD function explanation

lead(salary,2,0) over ( order by salary) will find the value after following 2 rows.

SQL >  select empno,salary,lead(salary,2,0) over ( order by salary) as lead_salary
from emp ;

EMPNO SALARY LEAD_SALARY
22 6500 7200
20 6900 7500
24 7200 7800
21 7500 0
23 7800 0

Also read about LAG function.

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

2 thoughts on “LEAD function Oracle”

  1. Thanks for the post.
    I just want to make sure the title of the second column in the query result is “SALARY” or “EMPNAME” ?

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