Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

SQL Server 2012 Functions - Lead and Lag

MSSQLTips author Rajendra Gupta By:   |   Read Comments (8)   |   Related Tips: More > Functions - System
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:

Create Test_table on the databse TestDB

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

Now the query for leadvalue and lagvalue will be

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


Last Update: 4/16/2012


About the author
MSSQLTips author Rajendra Gupta
Rajendra is a Consultant DBA with 4+ years of extensive experience in database administration including SharePoint databases.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
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


Friday, March 21, 2014 - 6:01:57 AM - Muthurajan Read The Tip

Hi,

     I want to update current record value with previous record values. can i use lag function in update statement or is there any other techniques to do it in sql server 2012?

 

Thanks

Muthu



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.