Lead and Lag functions in SQL Server 2012

Suggested Videos
Part 111 - Difference between rank dense_rank and row_number in SQL
Part 112 - Calculate running total in SQL Server 2012
Part 113 - NTILE function in SQL Server



In this video we will discuss about Lead and Lag functions.



Lead and Lag functions 

• Introduced in SQL Server 2012
• Lead function is used to access subsequent row data along with current row data
• Lag function is used to access previous row data along with current row data
• ORDER BY clause is required
• PARTITION BY clause is optional

Syntax

LEAD(Column_Name, Offset, Default_Value) OVER (ORDER BY Col1, Col2, ...)

LAG(Column_Name, Offset, Default_Value) OVER (ORDER BY Col1, Col2, ...)

• Offset - Number of rows to lead or lag.
• Default_Value - The default value to return if the number of rows to lead or lag goes beyond first row or last row in a table or partition. If default value is not specified NULL is returned.

We will use the following Employees table for the examples in this video


SQL Script to create the Employees table

Create Table Employees

(

     Id int primary key,

     Name nvarchar(50),

     Gender nvarchar(10),

     Salary int

)

Go

Insert Into Employees Values (1, 'Mark', 'Male', 1000)

Insert Into Employees Values (2, 'John', 'Male', 2000)

Insert Into Employees Values (3, 'Pam', 'Female', 3000)

Insert Into Employees Values (4, 'Sara', 'Female', 4000)

Insert Into Employees Values (5, 'Todd', 'Male', 5000)

Insert Into Employees Values (6, 'Mary', 'Female', 6000)

Insert Into Employees Values (7, 'Ben', 'Male', 7000)

Insert Into Employees Values (8, 'Jodi', 'Female', 8000)

Insert Into Employees Values (9, 'Tom', 'Male', 9000)

Insert Into Employees Values (10, 'Ron', 'Male', 9500)

Go

Lead and Lag functions example WITHOUT partitions : This example Leads 2 rows and Lags 1 row from the current row.

• When you are on the first row, LEAD(Salary, 2, -1) allows you to move forward 2 rows and retrieve the salary from the 3rd row.
• When you are on the first row, LAG(Salary, 1, -1) allows us to move backward 1 row. Since there no rows beyond row 1, Lag function in this case returns the default value -1.
• When you are on the last row, LEAD(Salary, 2, -1) allows you to move forward 2 rows. Since there no rows beyond the last row 1, Lead function in this case returns the default value -1.
• When you are on the last row, LAG(Salary, 1, -1) allows us to move backward 1 row and retrieve the salary from the previous row.

SELECT Name, Gender, Salary,

        LEAD(Salary, 2, -1) OVER (ORDER BY Salary) AS Lead_2,

        LAG(Salary, 1, -1) OVER (ORDER BY Salary) AS Lag_1

FROM Employees



Lead and Lag functions example WITH partitions : Notice that in this example, Lead and Lag functions return default value if the number of rows to lead or lag goes beyond first row or last row in the partition. 

SELECT Name, Gender, Salary,

        LEAD(Salary, 2, -1) OVER (PARTITION By Gender ORDER BY Salary) AS Lead_2,

        LAG(Salary, 1, -1) OVER (PARTITION By Gender ORDER BY Salary) AS Lag_1

FROM Employees