Click here to Skip to main content
15,868,016 members
Articles / Programming Languages / T-SQL
Tip/Trick

Lead and Lag Functions in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.79/5 (11 votes)
23 Feb 2016CPOL 35.4K   12   2
Lead and Lag Functions in SQL Server

Introduction

SQL Server 2012 introduced new analytical function LEAD() and LAG().

These functions accesses data from nth next row and nth previous row in the same result set without the use of a self-join.

  • LEAD(): used to access data from nth next row in the same result set without the use of a self-join
  • LAG(): used to access data from nth previous row in the same result set without the use of a self-join

Using the Code

Syntax

SQL
LEAD (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

Parameters

  • scalar_expression: column name for which value needs to be accessed
  • offset: nth previous (for lag) or next (for lead) row to access the column
  • default: default value to display if nth row does not exist

Example 1

SQL
DECLARE @Test_table TABLE(
    Year INT, Sale INT
)

INSERT INTO @Test_table VALUES
('2008',5000000), ('2009',5500000), ('2010',5250000), ('2011',6025000), ('2012',6200000)

SELECT    Year, Sale
        , LEAD(Sale) OVER (ORDER BY Year) AS [Next Year Sale]
        , LAG(Sale) OVER (ORDER BY Year) AS [Prev Year Sale]
        , LEAD(Sale, 2) OVER (ORDER BY Year) AS [2nd Next Year Sale]
        , LAG(Sale, 2) OVER (ORDER BY Year) AS [2nd Prev Year Sale]
        , LEAD(Sale, 2, 0) OVER (ORDER BY Year) AS [2nd Next Year Sale]
        , LAG(Sale, 2, 0) OVER (ORDER BY Year) AS [2nd Prev Year Sale]
FROM    @Test_table

OUTPUT

Image 1

Example 2 (With Partition By)

SQL
DECLARE @Test_table TABLE(
    Year INT, Zone VARCHAR(10),  Sale INT
)

INSERT INTO @Test_table VALUES
 ('2009', 'East', 5500000), ('2010', 'East', 5250000), _
 ('2011', 'East', 6025000), ('2012', 'East', 6200000)
,('2009', 'West', 5200000), ('2010', 'West', 5250000), _
('2011', 'West', 5525000), ('2012', 'West', 5700000)
,('2009', 'North', 4700000), ('2010', 'North', 4800000),_
('2011', 'North', 5000000), ('2012', 'North', 5050000)
,('2009', 'South', 7200000), ('2010', 'South', 7500000), _
('2011', 'South', 7800000), ('2012', 'South', 8000000)

SELECT    Zone, Year, Sale
        , LEAD(Sale) OVER (PARTITION BY Zone ORDER BY Year) AS [Next Year Sale]
        , LAG(Sale) OVER (PARTITION BY Zone ORDER BY Year) AS [Prev Year Sale]
        , LEAD(Sale, 2) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Next Year Sale]
        , LAG(Sale, 2) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Prev Year Sale]
        , LEAD(Sale, 2, 0) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Next Year Sale]
        , LAG(Sale, 2, 0) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Prev Year Sale]
FROM    @Test_table

OUTPUT

Image 2

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionZone needed Pin
NeverJustHere22-Nov-15 20:46
NeverJustHere22-Nov-15 20:46 
AnswerRe: Zone needed Pin
sandeepmittal1123-Nov-15 17:08
sandeepmittal1123-Nov-15 17:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.