By: Rajendra Gupta | Updated: 2012-04-16 | Comments (13) | Related: 1 | 2 | 3 | 4 | > 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:
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:
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
Next Steps
- Review more tips on functions
About the author
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2012-04-16
