Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

A new analytic functions in SQL Server Denali CTP3 - LEAD() and LAG()

Jul 25 2011 12:07AM by Paresh Prajapati   

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 .

Read More..   [0 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Paresh Prajapati
6 · 23% · 7379
10
 
1
 
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

3  Comments  

  • Itzik-ben-gan proposed this function and he created a connect item for this... Thanks to him. we have this function now....

    commented on Jul 25 2011 12:38AM
    Ramireddy
    2 · 40% · 12972
  • This feature was in ORACLE and was expecting it to be in SQL Server too. Most of the time I thought to have this while creating SQL queries, and happy that it has been added.

    commented on Jul 25 2011 1:40AM
    Manoj
    239 · 1% · 188
  • At last. I wish I could have this already in my actual project. I knew these very useful functions from previous oracle projects.

    commented on Aug 1 2011 3:06AM
    michael.schroeder
    2802 · 0% · 3

Your Comment


Sign Up or Login to post a comment.

"A new analytic functions in SQL Server Denali CTP3 - LEAD() and LAG() " rated 5 out of 5 by 10 readers
A new analytic functions in SQL Server Denali CTP3 - LEAD() and LAG() , 5.0 out of 5 based on 10 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]