How to Fetch Data from the Previous or Next Rows in the Resultset

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.