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

, 29 Jul 2011
Rate this:
Please Sign up or sign in to vote.
This tip demonstrates the use of LAG and LEAD functions in Oracle.
For SQL Server version of this tip, see: How to fetch data from the previous or next rows in the resultset[^]
 
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.
 
Oracle has two functions that address this problem: LAG (to look backwards) and LEAD (to look forward). Both can be instructed to look over several records.
 
To demonstrate these functions, let's create a simple test table:
CREATE TABLE LagTest (
   Event   VARCHAR2(100) NOT NULL,
   AddTime TIMESTAMP     NOT NULL
);
And then add 10 rows to the table with random timestamps in the future. Note that you need to have access to DBMS_RANDOM package. If that's not possible just replace the ABS( DBMS_RANDOM.Normal ) * 10 with some random number on every insert statement.
INSERT INTO LagTest VALUES 
   ('E#1', CURRENT_TIMESTAMP + ABS(DBMS_RANDOM.Normal) * 10);
INSERT INTO LagTest VALUES 
   ('E#2', CURRENT_TIMESTAMP + ABS(DBMS_RANDOM.Normal) * 10);
INSERT INTO LagTest VALUES 
   ('E#3', CURRENT_TIMESTAMP + ABS(DBMS_RANDOM.Normal) * 10);
INSERT INTO LagTest VALUES 
   ('E#4', CURRENT_TIMESTAMP + ABS(DBMS_RANDOM.Normal) * 10);
INSERT INTO LagTest VALUES 
   ('E#5', CURRENT_TIMESTAMP + ABS(DBMS_RANDOM.Normal) * 10);
INSERT INTO LagTest VALUES 
   ('E#6', CURRENT_TIMESTAMP + ABS(DBMS_RANDOM.Normal) * 10);
INSERT INTO LagTest VALUES 
   ('E#7', CURRENT_TIMESTAMP + ABS(DBMS_RANDOM.Normal) * 10);
INSERT INTO LagTest VALUES 
   ('E#8', CURRENT_TIMESTAMP + ABS(DBMS_RANDOM.Normal) * 10);
INSERT INTO LagTest VALUES 
   ('E#9', CURRENT_TIMESTAMP + ABS(DBMS_RANDOM.Normal) * 10);
INSERT INTO LagTest VALUES 
   ('E#10', CURRENT_TIMESTAMP + ABS(DBMS_RANDOM.Normal) * 10);
Now to the actual tip. If I want to have the following information:
  • What row I'm on?
  • What was the previous event?
  • What's the interval between the previous and this event?
  • What event pair is going to be next?
The query could look something like:
SELECT sub.Event AS ActualRow,
       CASE
          WHEN RowNum = 1 THEN 'First event'
          ELSE sub.PrevEvent || ' -> ' || sub.Event
       END AS LookBack,
       sub.AddTime - sub.PrevTime  AS Interval,
       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)   OVER (ORDER BY lt.AddTime) AS PrevEvent,
                  LAG(lt.AddTime) OVER (ORDER BY lt.AddTime) AS PrevTime,
                  LEAD(lt.Event)  OVER (ORDER BY lt.AddTime) AS NextEvent
           FROM   LagTest lt
           ORDER BY lt.AddTime) sub
Few notes to help to read the statement:
  • The row order is defined based on the AddTime OVER (ORDER BY lt.AddTime)
  • The inline view is used to eliminate the need to define LAG or LEAD multiple times
  • First and last rows are handled differently
I believe that the results explain the functionality much better:
ActualRow  LookBack       Interval                    LookForward
---------  -------------  --------------------------  -----------------
E#1        First event    -                           Next: E#1 -> E#6
E#6        E#1 -> E#6     +000000000 01:53:44.000000  Next: E#6 -> E#5
E#5        E#6 -> E#5     +000000000 16:16:49.000000  Next: E#5 -> E#8
E#8        E#5 -> E#8     +000000001 09:28:09.000000  Next: E#8 -> E#10
E#10       E#8 -> E#10    +000000001 15:13:38.000000  Next: E#10 -> E#3
E#3        E#10 -> E#3    +000000001 01:09:55.000000  Next: E#3 -> E#9
E#9        E#3 -> E#9     +000000001 16:03:21.000000  Next: E#9 -> E#7
E#7        E#9 -> E#7     +000000001 23:06:03.000000  Next: E#7 -> E#4
E#4        E#7 -> E#4     +000000004 10:23:42.000000  Next: E#4 -> E#2
E#2        E#4 -> E#2     +000000000 08:19:27.000000  Last event
Hopefully this helps you when having to handle data from several rows at the same time.

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

 
GeneralReason for my vote of 5 This is cool, didn't know that was p... PinmemberS Douglas25-Aug-11 13:27 
GeneralRe: Thanks, yes this sometimes saves a lot of work. PinmvpMika Wendelius25-Aug-11 19:37 
GeneralReason for my vote of 5 Interesting solution. Thanks. PinsubeditorWalt Fair, Jr.30-Jul-11 16:55 
GeneralRe: Thank you PinmvpMika Wendelius3-Aug-11 8:44 
GeneralReason for my vote of 5 Interesting solution! Typically I us... PinmemberDrABELL22-Feb-11 16:11 
GeneralRe: Thanks PinmvpMika Wendelius3-Aug-11 8:44 

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
Web03 | 2.8.140814.1 | Last Updated 29 Jul 2011
Article Copyright 2011 by Mika Wendelius
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid