Click here to Skip to main content
Click here to Skip to main content

How to fetch data from the previous or next rows in the resultset

, 31 Aug 2014
Rate this:
Please Sign up or sign in to vote.
This tip demonstrates the use of LAG and LEAD functions in SQL Server.

One common problem is the need to have data on a result set row from another row in the same result set. For example, in order to calculate an interval between dates on two separate rows, typically a self join is needed. This requires to add the same table (or set) twice into the query. With a simple query, this won't be any problem but if the result set is created using a more complex query, creating the same complexity twice can cause problems and of course create performance problems.

In SQL Server 2012, two functions with windowing support are introduced to address this problem: LAG (to look backwards) and LEAD (to look forward). Both can be instructed to look over several records, not just the previous or next.

First we need a simple table with some test data:

CREATE TABLE LagTest (
   Event   VARCHAR(100)  NOT NULL,
   AddTime DATETIME      NOT NULL
);

And some data with random dates in the future:

INSERT INTO LagTest VALUES ('E#1', CURRENT_TIMESTAMP + ABS( RAND() ) * 10);
INSERT INTO LagTest VALUES ('E#2', CURRENT_TIMESTAMP + ABS( RAND() ) * 10);
INSERT INTO LagTest VALUES ('E#3', CURRENT_TIMESTAMP + ABS( RAND() ) * 10);
INSERT INTO LagTest VALUES ('E#4', CURRENT_TIMESTAMP + ABS( RAND() ) * 10);
INSERT INTO LagTest VALUES ('E#5', CURRENT_TIMESTAMP + ABS( RAND() ) * 10);
INSERT INTO LagTest VALUES ('E#6', CURRENT_TIMESTAMP + ABS( RAND() ) * 10);
INSERT INTO LagTest VALUES ('E#7', CURRENT_TIMESTAMP + ABS( RAND() ) * 10);
INSERT INTO LagTest VALUES ('E#8', CURRENT_TIMESTAMP + ABS( RAND() ) * 10);
INSERT INTO LagTest VALUES ('E#9', CURRENT_TIMESTAMP + ABS( RAND() ) * 10);
INSERT INTO LagTest VALUES ('E#10', CURRENT_TIMESTAMP + ABS( RAND() ) * 10);

Now, in order to fetch the following information:

  • What row I'm on?
  • What was the previous event?
  • What's the datetime on previous row?
  • What's the interval in hours between the previous and this event?
  • What event pair is going to be next?

The query could be like:

SELECT sub.Event AS ActualRow,
       CASE
          WHEN ROW_NUMBER() OVER (ORDER BY sub.AddTime) = 1 THEN 'First event'
          ELSE sub.PrevEvent + ' -> ' + sub.Event
       END AS LookBack,
       sub.PrevTime,
       DATEDIFF( hour, sub.PrevTime, sub.AddTime)  AS IntervalInHours,
       CASE
          WHEN sub.NextEvent IS NOT NULL
             THEN 'Next: ' + sub.Event + ' -> ' + sub.NextEvent
          ELSE 'Last event'
       END AS LookForward
FROM   (   SELECT lt.Event,
                  lt.AddTime,
                  LAG(lt.Event, 1)   OVER (ORDER BY lt.AddTime) AS PrevEvent,
                  LAG(lt.AddTime, 1) OVER (ORDER BY lt.AddTime) AS PrevTime,
                  LEAD(lt.Event, 1)  OVER (ORDER BY lt.AddTime) AS NextEvent
           FROM   LagTest lt) AS sub

First, an inline view (named sub) is used to gather the data. Since LAG and LEAD are window functions, the ordering must be specified. In this example the AddTime column defines the order of the rows.

An example output from the inline view could look like the following:

Event AddTime                 PrevEvent PrevTime                NextEvent
----- -------                 --------- --------                ---------
E#5   2011-07-29 05:17:44.680 NULL      NULL                    E#1
E#1   2011-07-29 18:30:02.053 E#5       2011-07-29 05:17:44.680 E#4
E#4   2011-08-01 01:39:16.430 E#1       2011-07-29 18:30:02.053 E#10
E#10  2011-08-01 14:32:50.040 E#4       2011-08-01 01:39:16.430 E#2
E#2   2011-08-02 19:30:16.483 E#10      2011-08-01 14:32:50.040 E#8
E#8   2011-08-02 20:20:19.920 E#2       2011-08-02 19:30:16.483 E#9
E#9   2011-08-03 01:23:00.940 E#8       2011-08-02 20:20:19.920 E#6
E#6   2011-08-03 02:09:39.343 E#9       2011-08-03 01:23:00.940 E#7
E#7   2011-08-04 05:30:01.203 E#6       2011-08-03 02:09:39.343 E#3
E#3   2011-08-05 11:16:57.253 E#7       2011-08-04 05:30:01.203 NULL

The main query is basically just formatting, concatenation of event column data, interval calculation etc. The actual output would be like:

ActualRow LookBack    PrevTime                IntervalInHours LookForward
--------- --------    --------                --------------- -----------
E#5       First event NULL                    NULL            Next: E#5 -> E#1
E#1       E#5 -> E#1  2011-07-29 05:17:44.680 13              Next: E#1 -> E#4
E#4       E#1 -> E#4  2011-07-29 18:30:02.053 55              Next: E#4 -> E#10
E#10      E#4 -> E#10 2011-08-01 01:39:16.430 13              Next: E#10 -> E#2
E#2       E#10 -> E#2 2011-08-01 14:32:50.040 29              Next: E#2 -> E#8
E#8       E#2 -> E#8  2011-08-02 19:30:16.483 1               Next: E#8 -> E#9
E#9       E#8 -> E#9  2011-08-02 20:20:19.920 5               Next: E#9 -> E#6
E#6       E#9 -> E#6  2011-08-03 01:23:00.940 1               Next: E#6 -> E#7
E#7       E#6 -> E#7  2011-08-03 02:09:39.343 27              Next: E#7 -> E#3
E#3       E#7 -> E#3  2011-08-04 05:30:01.203 30              Last event

Having these two functions makes it easier to get data from next or previous rows.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mika Wendelius
Architect
Finland Finland
I've been a programmer since mid 80's using languages like assembler, C/C++, PL/I (mainframe environment), pascal, VB (I know, I know, no comments please) and C# and utilizing different techniques and tools.
 
However I'm specialized in databases and database modeling. Mostly I have used products like Oracle (from version 6), SQL Server (from version 4.2), DB2 and Solid Server (nowadays an IBM product).
 
For the past 10+ years my main concerns have been dealing with different business processes and how to create software to implement and improve them. At my spare time (what ever that actually means) I'm also teaching and consulting on different areas of database management, development and database oriented software design.

Comments and Discussions

 
GeneralMy vote of 5 PinprofessionalSanjay K. Gupta17hrs 40mins ago 
GeneralRe: My vote of 5 PinpremiumMika Wendelius17hrs 2mins ago 
GeneralMy vote of 5 PinmvpMaciej Los11-Jan-13 11:28 
GeneralRe: My vote of 5 PinpremiumMika Wendelius17hrs 2mins ago 
GeneralMy 5! PinmvpMaciej Los11-Jan-13 11:27 
GeneralRe: My 5! PinmvpMika Wendelius11-Jan-13 20:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 31 Aug 2014
Article Copyright 2011 by Mika Wendelius
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid