Analytic Function LEAD LAG In SQL Server, (Reading Next And Previous Row In SQL Server 2011 (“Code Named Denali”)

 

SQL Server 2011 Comes with 8 new function which are grouped under Analytic Function Group. Here Will We Discuss about only two functions listed below. These function must be used with Over and Partition By Clause

1. LEAD()
2. LAG()

Create Following Table With Script Provided Below

CREATE TABLE [dbo].[Records](
[Id] [int] IDENTITY(1,1) NOT NULL,[EmpName] [varchar](20) NULL,
    [AccountNo] [varchar](12) NULL,
    [IsActive] [tinyint] NULL,
    [Salary] [decimal](22, 2) NULL,
    [Department] [varchar](50) NULL
)
GO
INSERT [dbo].[Records] ([EmpName], [AccountNo], [IsActive], [Salary], [Department]) 
VALUES 
('Kuldeep Bisht', N'012398760987', 1, CAST(7680.00 AS Decimal(22, 2)), N'IT'),
('Vishal Verma', N'987026749876', 3, CAST(6700.00 AS Decimal(22, 2)), N'BPO'),
('Rakesh Kumar', N'876894326754', 1, CAST(4500.00 AS Decimal(22, 2)), N'TESTING'),
('Sandeep Verma', N'786798564329', 1, CAST(9800.00 AS Decimal(22, 2)), N'IT'),
('Revinder Singh', N'999867543987', 1, CAST(7850.00 AS Decimal(22, 2)), N'TESTING'),
('Seema Singh', N'876549879898', 2, CAST(4560.00 AS Decimal(22, 2)), N'BPO'),
('Ashutosh', N'785640987567', 1, CAST(7680.00 AS Decimal(22, 2)), N'IT'),
('Devesh Gupta', N'776623458123', 2, CAST(6600.00 AS Decimal(22, 2)), N'TESTING'),
('Satinder Negi', N'768098723456', 1, CAST(3500.00 AS Decimal(22, 2)), N'BPO'),
('Meenakshi', N'009812346785', 1, CAST(9800.00 AS Decimal(22, 2)), N'BPO'),
('Manoj Pandey', N'767689900145', 3, CAST(7800.00 AS Decimal(22, 2)), N'IT'),
('Sanjay Rana', N'980765430974', 1, CAST(8800.00 AS Decimal(22, 2)), N'SALE')
Go

Lets Explore Analytic Functions

LEAD()

Syntax :

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

LEAD Function : It is used to get result from next row in current row. You can define the row number to read from current row. 

Offset : Row Number To Read From Current Row. Default Value is 1 i.e. Next Row.

Default : if Next Row Is not Found then Return Default Value Else Null Is Returned

Select Department,EmpName,Salary,
LEAD(Salary) Over (Partition By Department Order By Salary Desc) As NextHighest,
LEAD(Salary,2) Over (Partition By Department Order By Salary Desc) As Second_Next_Highest
From Records

As you can see in above result of lead function each row shows value from its next row. don’t you think its very exciting without using self join or any other complex query we are able to get next row values. Its really simple and easy to use.

In above query you saw that each row reads value from next row In (Next_Highest) Column and where next row doesn’t exists against a department it show null values. you can handle and change this default behavior of LEAD() function. by using offset  and default parameter of LEAD() function.

LAG()

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

LAG Function : It is used to get result from Previous row in current row. You can define the row number to read from current row. 

Offset : Row Number To Read From Current Row. Default Value is 1 i.e. Previous Row.

Default : if Previous Row Is not Found then Return Default Value Else Null Is Returned.

Select Department,EmpName,Salary,
LAG(Salary) Over (Partition By Department Order By Salary ) As Previous_Highest,
LAG(Salary,2) Over (Partition By Department Order By Salary) As Second_Previous_Highest
From Records

Output ;

So Now Its Very Easy To Get Or Check Values From Previous and Next Rows With Ease and Little Effort.