LEAD() and LAG() are interesting functions introduced in SQL Server 2011 CTP3, which will be used to get previous and subsequent rows values
LEAD() : Used to access data from a subsequent row in the same result set without the use of a self-join.
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 .
Read More..
[0 clicks]