SQL Server LAG() function Overview

At many instances, user would like to access data of the previous row or any row before the previous row from the current row.
To solve this problem SQL Server’s LAG() window function can be used.

LAG() :
SQL Server provides LAG() function which is very useful in case the current row values need to be compared with the data/value of the previous record or any record before the previous record. The previous value can be returned on the same record without the use of self join making it straightforward to compare.

Syntax :

LAG (scalar_expression [, offset] [, default])  
OVER ( [ partition_by ] order_by )  

Where :

  1. scalar_expression –
    The value to be returned based on the specified offset.



  2. offset –
    The number of rows back from the current row from which to obtain a value. If not specified, the default is 1.

  3. default –
    default is the value to be returned if offset goes beyond the scope of the partition. If a default value is not specified, NULL is returned.

  4. over ( [ partition_by] order_by) –
    partition_by divides the result set produced by the FROM clause into partitions to which the function is applied. If you omit PARTITION BY clause, the function treats whole result set as a single group. By default order_by clause sorts in ascending order.

Example-1:

SELECT Organisation, [Year], Revenue,
LAG (Revenue, 1, 0) 
OVER (PARTITION BY Organisation ORDER BY [Year]) AS PrevYearRevenue  
FROM Org 
ORDER BY Organisation, [Year]; 

Output –

Organisation Year Revenue PrevYearRevenue
ABCD News 2013 440000 0
ABCD News 2014 480000 440000
ABCD News 2015 490000 480000
ABCD News 2016 500000 490000
ABCD News 2017 520000 500000
ABCD News 2018 525000 520000
ABCD News 2019 540000 525000
ABCD News 2020 550000 540000
Z News 2016 720000 0
Z News 2017 750000 720000
Z News 2018 780000 750000
Z News 2019 880000 780000
Z News 2020 910000 880000

In the above example, We have 2 TV News Channel whose Current and Previous Year’s Revenue is presented on the same row using the LAG() function. As You can see that the very first record for each of the TV News channels don’t have previous year revenues so it shows the default value of 0. This function can be very useful in yielding data for BI reports when you want to compare values in consecutive periods, for e.g. Year on Year or Quarter on Quarter or Daily Comparisons.

Example-2:

SELECT Z.*,  (Z.Revenue - z.PrevYearRevenue) as YearonYearGrowth
from (SELECT Organisation, [Year], Revenue,
      LAG (Revenue, 1) 
      OVER (PARTITION BY Organisation ORDER BY [Year] ) AS PrevYearRevenue 
      FROM Org) Z ORDER BY Organisation, [Year]; 

Output –

Organisation Year Revenue PrevYearRevenue YearOnYearGrowth
ABCD News 2013 440000 NULL NULL
ABCD News 2014 480000 440000 40000
ABCD News 2015 490000 480000 10000
ABCD News 2016 500000 490000 10000
ABCD News 2017 520000 500000 20000
ABCD News 2018 525000 520000 5000
ABCD News 2019 540000 525000 15000
ABCD News 2020 550000 540000 10000
Z News 2016 720000 NULL NULL
Z News 2017 750000 720000 30000
Z News 2018 780000 750000 30000
Z News 2019 880000 780000 100000
Z News 2020 910000 880000 30000

In the above example, We can similarly calculate Year On Year Growth for the TV News Channel. Also, one thing to notice in this example is we haven’t supplied any default parameter to LAG(), and hence the LAG() function returns NULL in case there are no previous values.

The LAG() function can be implemented at the database level and BI Reporting solutions like Power BI and Tableau can avoid using the cumbersome measures at the reporting layer.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

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.