SQL Reference ยท Window Functions
SQL LAG() and LEAD() functions
Reference for SQL LAG() and LEAD(): read a value from a previous or following row, offsets, default values, partitions, NULL at the edges and change calculations.
LAG() and LEAD() are window functions that read a column from another row in the same partition without a self join. LAG(expr) looks backward at the previous row in the window order; LEAD(expr) looks forward at the next row. A second argument chooses how many rows to move, and a third supplies a default for when there is no such row.
They are the natural tool for "difference from the previous period", "time since the last event", "did the value change" and "what comes next". Because they use the ORDER BY of the window, the meaning of "previous" is always defined by you.
SELECT id, ordered_at, LAG(ordered_at) OVER (ORDER BY ordered_at) AS previous_order, LEAD(ordered_at) OVER (ORDER BY ordered_at) AS next_order FROM orders;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
The last row says none only because the WHERE id <= 4 filter removed the products that would follow it; window functions see the rows left after WHERE.
Syntax
LAG(expression [, offset [, default]]) OVER ( [ PARTITION BY ... ] ORDER BY ... ) LEAD(expression [, offset [, default]]) OVER ( [ PARTITION BY ... ] ORDER BY ... )
| Part | Description |
|---|---|
expression |
The value to read from the other row: a column or any expression. |
offset |
Optional. How many rows back (LAG) or forward (LEAD). Default 1. A non-negative integer; 0 means the current row. |
default |
Optional. Returned when the target row is outside the partition. Default NULL. |
ORDER BY |
Required in practice: it defines "previous" and "next". |
Returns: the value of expression at the target row, with the type of expression, or default / NULL if there is no such row. A frame clause is not allowed: these functions ignore frames and always see the whole partition.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
LAG, LEAD |
3.25+ | 8.4+ | 8.0+ | 2012+ | Yes |
IGNORE NULLS / RESPECT NULLS |
No | No | No | 2022+ | Yes |
| Offset 0 allowed | Yes | Yes | Yes | Yes | Yes |
-- Oracle, SQL Server 2022: skip NULLs when looking back SELECT day, LAG(reading) IGNORE NULLS OVER (ORDER BY day) AS last_known FROM sensor;
Where IGNORE NULLS is missing, emulate it with a correlated subquery or a running MAX() of a marker, as taught in the lag and lead lesson.
Usage notes
- The edges are
NULL. The first row has noLAGand the last has noLEAD. Any arithmetic with thatNULLis alsoNULL, so useCOALESCE()or the third argument. - A
NULLstored in the data is indistinguishable from "no row" unless you use the default argument carefully or test the row number. - Partitions are walls. Add
PARTITION BYso the first order of customer 8 does not look back at the last order of customer 3. - Order matters, and ties need a tie-breaker. Add a unique column to the
ORDER BY, or the "previous" row among equal values is arbitrary. - Change detection:
value <> LAG(value) OVER (...)is true when a value changed. It isNULLon the first row, so wrap withCOALESCE(..., 1)or handle it separately. - Filtering on the result requires an outer query, because window functions are not allowed in
WHERE. - Replaces self joins. One
LAG()is shorter and usually faster than joining a table to itself onid - 1, which also breaks when ids have gaps. offsetmust not be negative in most databases; use the opposite function instead.