SQL Server Denali : Lead and Lag

LEAD() and LAG() are two interesting functions introduced in SQL Server Denali.


LEAD() can be used to access data from a subsequent row in the same result set and its counterpart LAG() function can be used to access data from a previous row in the result set without using a self-join. 


Syntax: LEAD/LAG (scalar_expression [,offset] [,default]) OVER ([partition_by_clause] order_by_clause)
/* Create Table */
CREATE TABLE ExamResults
(SubjectName varchar(25), Student  varchar(50), Number int)
-- Inserting sample records
INSERT INTO ExamResults 
VALUES
('Maths', 'Student 1', 45),
('Physics', 'Student 2', 45),
('Physics', 'Student 1', 50),
('Chemistry', 'Student 3', 20),
('Physics', 'Student 3', 35),
('Biology', 'Student 1', 20),
('Biology', 'Student 2', 60),
('Biology', 'Student 3', 65),
('Chemistry', 'Student 1', 75),
('Biology', 'Student 4', 30)
GO
/* Query using LEAD() and LAG() */
SELECT Student , SubjectName, Number, 
LAG(Number,1,0) OVER(PARTITION BY SubjectName ORDER BY Number) as LG,
LEAD(Number,1,0) OVER(PARTITION BY SubjectName ORDER BY Number) as LD
FROM ExamResults ORDER BY SubjectName, Number
GO
/*
OUTPUT :
Student      SubjectName   Number   LG  LD
-------------   --------------------  -----------   ----  ----
Student 1   Biology                20      0   30
Student 4   Biology        30    20   60
Student 2   Biology        60    30   65
Student 3   Biology        65    60     0
Student 3   Chemistry             20      0   75
Student 1   Chemistry        75    20     0
Student 1   Maths        45      0     0
Student 3   Physics        35      0   45
Student 2   Physics        45    35   50
Student 1   Physics        50    45     0
*/