solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

Idera - SQL compliance manager

Low-impact SQL Server auditing of all user activity and data changes

Real time auditing
Flexible collection filters
Customizable alerts on suspect activity

Learn more!

SQL Server 2012 Functions - Lead and Lag

By: Rajendra Gupta | Read Comments (7) | Related Tips: More > Functions - System

Free SQL Learning - Backup compression and storage deduplication: A perfect match?

Problem

SQL Server 2012 introduces two new analytical functions, LEAD() and LAG(). In this tip we will be exploring these functions and how to use them.

Solution

These functions access data from a subsequent row (lead) and previous row (lag) in the same result set without the use of a self-join.

The syntax for the Lead and Lag functions is:

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

Let me explain using this example which creates table Test_table in database TestDB and inserts some data.

CREATE DATABASE [TestDB]
 
--Create testable to hold some data
CREATE TABLE [dbo].[Test_table](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [Department] [nchar](10) NOT NULL,
 [Code] [int] NOT NULL,
 CONSTRAINT [PK_Test_table] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--Insert some test data
insert into Test_table values('A',111)
insert into Test_table values('B',29)
insert into Test_table values('C',258)
insert into Test_table values('D',333)
insert into Test_table values('E',15)
insert into Test_table values('F',449)
insert into Test_table values('G',419)
insert into Test_table values('H',555)
insert into Test_table values('I',524)
insert into Test_table values('J',698)
insert into Test_table values('K',715)
insert into Test_table values('L',799)
insert into Test_table values('M',139)
insert into Test_table values('N',219)
insert into Test_table values('O',869)

Our table data will look like this:

Now the query for lead value and lag value will be:

SELECT id,department,Code,
LEAD(Code,1) OVER (ORDER BY Code ) LeadValue,
LAG(Code,1) OVER (ORDER BY Code ) LagValue
FROM test_table

In the above example, for the first row the Lead value is the value of the next row because the offset is set to 1. The Lag value is NULL because there were no previous rows.

Now if we change the Lead offset to 2 and Lag offset to 3 the output will be as follows:

If we change Lead offset to 2 and Lag offset to 3 the output will be:

One thing to note is that NULL values appear, because there are not values for the Lag or Lead. To replace NULL values with zero add 0 in Lead\Lag function as shown below.

SELECT id,department,Code,
LEAD(Code,2,0) OVER (ORDER BY Code ) LeadValue,
LAG(Code,3,0) OVER (ORDER BY Code ) LagValue
FFROM test_table

$replace NULL with ‘0’ add 0 in Lead\Lag function.$

Next Steps

Review these other SQL Server 2012 Tips

Last Update: 4/16/2012

About the author

Become a paid author

Comments and Feedback:

Monday, April 16, 2012 - 8:40:09 AM - Hassan Parthasarathy

Read The Tip

Good to know;

Thanks for the post!

Partha

Monday, April 16, 2012 - 9:01:20 AM - jeevan

Read The Tip

handy functions....but why did it take so long........?

Tuesday, April 17, 2012 - 2:09:51 AM - Lakki

Read The Tip

Thanks for your post and Good to know new Functions

Saturday, May 05, 2012 - 9:24:32 PM - Tony C	Read The Tip
Interesting. I'm trying to think of a practical use for this functionality. Thoughts?

Saturday, May 05, 2012 - 9:50:04 PM - Tony C	Read The Tip
Seems to be borrowed to some extent from Oracle's analytical functions...LEAD, LAG *(, , ) OVER ()*

Friday, May 11, 2012 - 9:36:48 AM - Andrew Davis

Read The Tip

To Tony C - I have used something similar to create an effective date range.

I have values that are changing on an occasional basis (readings from a power station). By using the Lead function in Oracle, I have been able to generate a date range (i.e. a From_Datetime and To_Datetime columns). This proved very useful for retrieving the value for a particular point in time. The alternative (using a self join and MIN statement) was very slow on a 250m row table!

The Oracle SQL is here (should be self explanatory):

SELECT
z.msgcode
,z.Station
,z.value
,z.effectivefrom effectivefrom
,CASE
WHEN ( nextStation=Station AND nextmsgcode=msgcode)
THEN nextroweffectivedate
ELSE sysdate+9999 END effectiveto
FROM
(select
x.*
,lead(x.effectivefrom,1) over (order by msgcode,Station,effectivefrom) as nextroweffectivedate
,lead(x.BMUNITCODE,1) over (order by msgcode,Station,effectivefrom) as nextStation
,lead(x.msgcode,1) over (order by msgcode,bmunitcode,effectivefrom) as nextmsgcode
from
stationvalue x
) z

Friday, May 11, 2012 - 9:38:24 AM - Andrew Davis

Read The Tip

Sorry, typo in the SQL:

SELECT
z.msgcode
,z.Station
,z.value
,z.effectivefrom effectivefrom
,CASE
WHEN ( nextStation=Station AND nextmsgcode=msgcode)
THEN nextroweffectivedate
ELSE sysdate+9999 END effectiveto
FROM
(select
x.*
,lead(x.effectivefrom,1) over (order by msgcode,Station,effectivefrom) as nextroweffectivedate
,lead(x.station,1) over (order by msgcode,Station,effectivefrom) as nextStation
,lead(x.msgcode,1) over (order by msgcode,station,effectivefrom) as nextmsgcode
from
stationvalue x
) z

Sponsor Information

SQL Server having some performance issues? Idera SQL check. FREE SQL Server enhancement.

NEW! Top 5 hard-earned lessons of a DBA from Grant Fritchey & the DBA Team. Read it now

Need SQL Server help and not sure where to turn? Reach out to expert consultants in the USA for a Health Check.

Unlock the power of the Transaction log to discover unauthorized changes and recover lost data

Free Webinar - Backup compression and storage deduplication: A perfect match?