Search This Blog

Lead and Lag Function in SQL


I often heard in various forums about the use of lead and Lag Function, since most of the developers are still working on 2008 R2, so they are not aware of these two Lead and Lag functions.Below I will explain the Lead & Lag Function with a simple example

SQL Server 2012 introduces new analytical function LEAD() and LAG(). These functions accesses data from a subsequent row (for lead) and previous row (for lag) in the same result set without the use of a self-join.

First I will explain what exactly Lead and Lag is with an example

Below is a list of 5 records from A to E

A, B, C, D and E

 As per Lead definition- “Lead Accesses data from a subsequent row in the same result set without the use of a self-join”

So in our example Since A is the first value so B is leading A Similarly C is leading B Next D is leading C and E is leading D and since E is the last Record and doesn’t have any subsequent Row so the Lead Value of E is NULL as shown in below table structure

Name   LeadValue
A          B
B          C
C          D
D          E
E          NULL

Now using the same example I will show the Lag values

As per LAG definition- “Accesses data from a previous row in the same result set without the use of a self-join”

So in our example Since A is the first value so A doesn’t have any lag value so it will have LAG Value as NULL. Now Since A is coming before B so B has a lag value as A Similarly C has lag values as B, next D has a Lag value which is C, and E has lag value which is D as shown in below table structure

Name   LagValue
A          NULL
B          A
C          B
D          C
E          D

Now, I will use the above example to use Lead and Lag Function in SQL Server

--Test table Script with above example

create table #t(id int identity(1,1), name varchar(100))

insert into #t values('A'),('B'),('C'),('D'),('E')

--Using Lead & Lag Function to Get the Leading & Lagging value
SELECT *,
LEAD(Name) OVER (ORDER BY ID) LeadValue,
LAG (Name) OVER (ORDER BY ID) LagValue
FROM #t




This is just a basic and very simple example of Lead and Lag function, in real time scenario’s there will be many situations where these functions are quite handy.

NOTE: Lead & Lag functions only works with SQL 2012 & above versions



No comments:

Post a Comment