LAG

LAG provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position.

Usage

LAG(value-expr[, offset][, default])

Notes

LAG is applied to the value-expr.

If you do not specify offset, then its default is 1. The optional default value is returned if the offset goes beyond the scope of the window. If you do not specify default, then its default value is NULL.

Example: Use LAG to access Rows around the Current Row

Frequently you want to access data not only from the current row but the also the previous or next row. For example, the following query shows the details of the current call and the date of the previous call made by the subscriber:

SELECT subscriber, cdate, ctime,
    LAG(cdate, 1) OVER(
        PARTITION BY subscriber
        ORDER BY cdate, ctime) prev_call
FROM CALLS
ORDER BY 1, 2, 3

SUBSCRIBER

CDATE

CTIME

PREV_CALL

01277824068

2007-09-14

15:31:57

<Null>

01277824068

2007-09-14

15:32:57

2007-09-14

01277824068

2007-09-17

15:33:57

2007-09-14

01277824068

2007-09-18

15:38:58

2007-09-17

01277824068

2007-09-19

15:39:58

2007-09-18

01285720653

2007-09-17

15:34:58

<Null>

01285720653

2007-09-17

15:35:58

2007-09-17

01285720653

2007-09-18

15:36:58

2007-09-17

01285720653

2007-09-18

15:37:58

2007-09-18

01344300770

2007-09-19

14:11:36

<Null>

The LAG (and LEAD) routines can be considered a way to index a partitioned group. Using these functions you can access any individual row preceding or following the current record in an ordered partition.

See the overview for details of the data used in this example.