Lead and Lag functions in SQL Server 2012

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

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

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

Text version of the video

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

[clintbanzet]

Absolutely the best explanation I have found on this topic. OVER, ORDER BY, PARTITION BY, always confused me. Thank you for taking the time to explain it so thoroughly. And you did it quickly with no unnecessary waiting or delays. Thank you so much. Also in my mind, Microsoft has the names for LEAD and LAG swapped. After hearing your explanation though, I can at least see where they were coming from.

[elektrote]

I rarely comment on videos but bro, my outmost respect and gratitude to you for this simple but concise explanation.

[krzysztofs]

Venkat rules! Thank you for educating community! You are master of SQL Server. I'm the greatest fan of your tutorial. I watched all your videos. Thank U for all your job.

[ankitgangwar]

simple and brilliant explanation! thanks

[dnadelson]

Thank you so much for this brilliant demonstration!

[rogersantos]

Simple explaning that tells everything! thanks again !!!

[Liquidsouls]

This is SUCH a great video! thanks for the explanation!!!

[renobramantyo]

old but gold, this is the best explanation for me. thank you so much brother!

[upendrachaube]

Appreciate your great efforts and awesome teaching skills 👍🏻

[nikhilmekala]

Kudvenkat explain these concepts in a simple way? Absolutely!

[malvikachandhok]

Thank you for your explanation! It was very easy to understand through your examples! :)

[BobbeeSingh]

Very good video. Please don't take it the wrong way but usually people form India makin the videos will mispronounce the words and/or not enunciate them properly or they talk really fast. Your pronunciation, speed and enunciations of words was well done. Thanks

[mohsenhs]

As always, another amazing tutorial, thanks

[TheStarryboy]

Thank you Venkat Sir. You are the best.

[andrerussell]

Superb explanation of these functions, thank you.

[alechenry]

thank you for the video - extremely clear

[sohanyennu]

Thanks a ton...This function eases the number code lines...

[dollarmenu]

great video thank you - one of the best that i found

[jackchatten]

Great simple explanation. Very helpful

[shaikhs]

Thanks Venkat

I dont say about that

U r superb talented person in orcale