SQL Server LEAD() function Overview

At many instances, user would really like to check the worth of the present row with the worth of the subsequent row. to resolve this problem in SQL Server’s LEAD() window function are often used.

LEAD() :
Function provides access to a row at a set physical offset following this row. LEAD() function will allows to access data of the following row, or the row after the subsequent row, and continue on.

Syntax :

LEAD(return_value, offset [, default])  
OVER (
   [PARTITION BY partition_expression]
   ORDER BY sort_expression [ASC | DESC]
)

Where :

  1. return_value –
    The return_value of the subsequent row supported a specified offset. The return_value must be one value.



  2. offset –
    The offset is that the number of rows forward from the present row from where to access data. The offset should be a positive integer. If you don’t define the default value of offset is 1.

  3. default –
    The LEAD() function results default if offset is beyond the scope of the partition. If not defined, default is NULL.

  4. PARTITION BY clause –
    The PARTITION BY clause is optional, it differs rows of the result set into partitions to where the LEAD() function is used.

  5. ORDER BY clause –
    The ORDER BY clause define logical order of the rows in each partition to where the LEAD() function is used.

Example-1:
Let us suppose we have below table named “CompanySales” :

Select * 
from CompanySales;
Company Year Amount
ABC Ltd. 2015 5000
XYZ Ltd. 2015 4600
ABC Ltd. 2017 5500
ABC Ltd. 2016 5400
XYZ Ltd. 2016 6500
ABC Ltd. 2018 5400
XYZ Ltd. 2017 4700
XYZ Ltd. 2018 5400

Example-2:

SELECT TOP 10 [Company], [Year], [Amount],
LEAD(Amount, 1) OVER (
PARTITION BY Company
ORDER BY Year DESC
) AS Lead_amount
FROM [CompanySales] ;

Output –

Company Year Amount Lead_amount
ABC Ltd. 2018 5400 5500
ABC Ltd. 2017 5500 5400
ABC Ltd. 2016 5400 5000
ABC Ltd. 2015 5000 NULL
XYZ Ltd. 2018 5400 4700
XYZ Ltd. 2017 4700 6500
XYZ Ltd. 2016 6500 4600
XYZ Ltd. 2015 4600 NULL
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.