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 TH-cam channel. Hope you can help.
/ @aarvikitchen5572
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
csharp-video-tu...
Slides
csharp-video-tu...
All SQL Server Text Articles
csharp-video-tu...
All SQL Server Slides
csharp-video-tu...
All Dot Net and SQL Server Tutorials in English
www.youtube.co...
All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic

[@elektro9te]

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

[@clintbanzet2958]

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.

[@yaravallirohit8014]

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.

[@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

[@nikhilmekala3446]

Kudvenkat explain these concepts in a simple way? Absolutely!

[@ankitgangwar9082]

simple and brilliant explanation! thanks

[@renobramantyo5069]

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

[@Ak20231]

Best explanation and very simple

[@rogersantos2605]

Simple explaning that tells everything! thanks again !!!

[@user-rj2wc8uo6t]

Very much of clear explanation

[@karthikyammanur7881]

Hi Venkat, As always you are the best. My career did build on your videos. I was asked in a interview on how to build the same as lead and lag using self joins and sub queries without using these window functions. Could you please provide your insight on this? Thanks much for everything you have provided to the techies across the world.

[@malvikachandhok8571]

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

[@upendrachaube3192]

Appreciate your great efforts and awesome teaching skills 👍🏻

[@smgt90]

Excellent video

[@VIJAYKUMAR-qr8st]

Good explanation 👍

[@dnadelson]

Thank you so much for this brilliant demonstration!

[@timstrack4573]

Excellent!! You explain the function with simple explanation.

[@Csharp-video-tutorialsBlogspot]

Hello Tim - Thank you very much for the feedback. Means a lot. I have included all the sql tutorial videos, slides and text articles in sequence on the following page. Hope you will find it handy.
www.pragimtech.com/courses/sql-server-tutorial-for-beginners/
When you have some time, can you please leave your rating and valuable feedback on the REVIEWS tab. It really helps us. Thank you. Good luck and all the very best with everything you are doing.

[@sourabroy7787]

Great explanation!

[@TheStarryboy]

Thank you Venkat Sir. You are the best.

[@gijane2587]

Crisp and to the point

[@ushareddy3323]

Thanks for your efforts sir. Keep doing more vedios on SQL 🙏

[@shaikhs6299]

Thanks Venkat
I dont say about that
U r superb talented person in orcale

[@anusuyashanmugam957]

Very nice video

[@andrerussell28]

Superb explanation of these functions, thank you.

[@ahmetalperenyildirim6184]

It was clear explanation, thank you sir!

[@ShubhamRai06]

Great

[@devarakondasankararao3802]

Easy understanding thank you Venkat sir

[@DebashishP82]

excellent explanation. many thanks for the video

[@ayselceferzade8587]

Thanks a lot for explanation!

[@sohanyennu3750]

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

[@swevirgo]

Thanks fr the simple and easy explanation

[@jackchatten3777]

Great simple explanation. Very helpful

[@couragemath9333]

Excellent video. Thank you sir

[@alinemath5030]

Thank you!

[@Liquidsouls]

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

[@anjanummadi4808]

Hi Venkat,
We are really gratefull to you. Thanks for your time to provide all these vedios for the learners. could you please let us know if you have any vedios for control statements like FOR,WHILE, DO WHILE and IF ELSE Statements with examples.
Eagerly waiting for your response.
Can you also let me know if you have any clssess to attend by paying money to learn PLSQL?

[@muhammadrehbarsheikh8498]

Thanks venkat sir!

[@muhammadrehbarsheikh8498]

Thanks for replying! God bless you sir!

[@ashokstephen2175]

Hai venkat sir...thank you so much sir.
v r getting good knowledge from ur videos.
can u pls share realtime videos on ETL,BI testing.

[@dollarmenu9618]

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

[@MohammedDawoodAnsari]

Thank you so much for the video :)

[@mohsenhs]

As always, another amazing tutorial, thanks

[@alonben-david4929]

thanks It was very helpful

[@rajmothe3716]

Thank you Venkat Sir !

[@bernsbats5995]

WOW! Awesome functions to use!

[@emchammer1815]

now it makes sense. thanks buddy

[@alechenry9994]

thank you for the video - extremely clear

[@JorgeGonzalez-pq7wh]

Great video, thanks!

[@raqibul1000]

Thanks a Billion.Thank you so much.

[@shortsmore278]

Fantastic !!!!

[@govindrajkannan7757]

thanks once again sir

[@Csharp-video-tutorialsBlogspot]

+govindraj kannan Thanks a million for taking time to give feedback. I am glad you found the videos useful.
I am Venkat (Software Architect) with 13 years of experience building highly transactional software applications
Free Dot Net & SQL Server videos for web developers
th-cam.com/users/kudvenkatplaylists?view=1&sort=dd
If you need DVDs for offline viewing, you can order them using the link below
www.pragimtech.com/Order.aspx
Code Samples, Text Version of the videos & PPTS on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use our channel
th-cam.com/video/y780MwhY70s/w-d-xo.html
Want to receive email alerts, when new videos are uploaded, please subscribe to our channel using the link below
th-cam.com/users/kudvenkat
Please click the THUMBS UP button below the video, if you think you liked them
Thank you for sharing these links with your friends
Best
Venkat

[@saurabhchauhan232]

If you provide some real time use of this kind of function that will be very helpful sir. if you speak the scenario that would be sufficient sir. thanks a lot for all this videos :)

[@eman1987sy]

thanks

[@sayi77]

perfect as ur other lessons.

[@ACheng369]

cool job :)

[@TheEsisia]

You are awesome!

[@ravitejapendyala9078]

In lag function if we don't give any parameters the default lag step value 1 is right?
If we give Like lag(id) it will down 1 step below for all the rows

[@chengareddy4269]

plz make one video for
provide performance tuning in SQL server

[@jeanhyppolite1101]

you the man

[@prafullakumarseelam6500]

How can use the lag function in CASE -WHEN statement ? I need to perform an increment by 1 of the previous value if my condition is true else it should retain the same value. I am using lag in case-when statement but not getting the desired output. Please help.

[@adityaraj-jr8ph]

yo bro thanks

[@norpriest521]

I don't find these two functions useful

[@justinli19901027]

this man is a god!

[@levanmakharadze359]

Hello
I'm getting this error when I'm trying to use same thing on MYSQL DATAGRIP:
[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ORDER BY Salary) AS LEAD
What can I do here to solve this problem? Any help will be appreciated

[@c-sharptricks9899]

how to use where clause at end?

[@vidharthkate1897]

Why did Lead doesn't throw error even when it is a windows function?

[@akashgautam1909]

Could someone please help me know the pronunciation of this gentleman's name?

[@raquib7167]

What is real use of this ?

[@sisgir7280]

who is watching this in 2022

[@gotchihaeyo1825]

Me me me

[@krzysztofs8535]

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.

[@anjanummadi4808]

Hi Venkat,
We are really gratefull to you. Thanks for your time to provide all these vedios for the learners. could you please let us know if you have any vedios for control statements like FOR,WHILE, DO WHILE and IF ELSE Statements with examples.
Eagerly waiting for your response.
Can you also let me know if you have any clssess to attend by paying money to learn PLSQL?