LEAD() and LAG() are interesting functions introduced in SQL Server 2011 CTP3, which will be used to get previous and subsequent rows values

1. LEAD() : Used to access data from a subsequent row in the same result set without the use of a self-join.

2. LAG() : Used to access data from a previous row in the same result set without the use of a self-join.

It will be returned a values based on specified offset where offset is the number of rows forward/back from current rows to get value. The default value is 1.

-- Creating table
CREATE TABLE [Results]
(
[Subject] varchar(10),
[Student] varchar(50),
[Marks] int
)

-- Inserting sample records 
INSERT INTO [Results]
VALUES
('Maths','Student1',45),
('Physics','Student2',45),
('Physics','Student1',50),
('Chemistry','Student3',20),
('Physics','Student3',35),
('Biology','Student1',20),
('Biology','Student2',60),
('Biology','Student3',65),
('Chemistry','Student1',75),
('Biology','Student4',30)
GO

-- Querying using LEAD() and LAG()
SELECT 
[Student],
[Subject],
[Marks],
LEAD([Marks],1,0) OVER(PARTITION BY [Subject] ORDER BY [Marks]) as LD,
LAG([Marks],1,0) OVER(PARTITION BY [Subject] ORDER BY [Marks]) as LG
FROM [Results]
ORDER BY [Subject], [Marks]
GO

/*
OUTPUT :
Student  Subject    Marks  LD	LG
Student1 Biology    20     30   0
Student4 Biology    30     60   20
Student2 Biology    60     65	30
Student3 Biology    65     0    60
Student3 Chemistry  20     75	0
Student1 Chemistry  75     0 	20
Student1 Maths      45	   0	0
Student3 Physics    35     45	0
Student2 Physics    45     50	35
Student1 Physics    50     0	45
*/

You can read my earlier post for the functions here, http://beyondrelational.com/justlearned/posts/418/percentilecont-and-percentiledisc-a-new-analytic-function-in-sql-server-denali-ctp3.aspx .

Published under: SQL Server Tips ·  ·  ·  ·