LAG / LEADFunctions
Buy Me A Coffee

Access the value from a row before (LAG) or after (LEAD) the current row within an ordered window partition — without a self-join.

Accesses data from a preceding (LAG) or following (LEAD) row within a partition without a self-join. Essential for period-over-period comparisons, change detection, and computing differences between consecutive rows.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0 LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). Offset defaults to 1. Default value returned when offset goes out of bounds (instead of NULL).
PostgreSQL ✓ Supported 8.4 LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). Supports any offset and a default value for when the offset goes beyond partition bounds.
SQL Server ✓ Supported 2012 LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). Added in SQL Server 2012. Offset must be a non-negative integer constant.
Oracle ✓ Supported 8i LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). One of the earliest implementations — Oracle has had these since 8i.
SQLite ✓ Supported 3.25.0 LAG() and LEAD() fully supported since SQLite 3.25.0 (September 2018).

Details

LAG/LEAD replaced the classic self-join pattern (join a table to itself on id-1). The optional default argument (third parameter) controls what's returned at partition boundaries — without it you get NULL, which can break calculations. SQL Server didn't add these until 2012; Oracle had them since 8i.

Standard Syntax

SELECT order_date, amount, LAG(amount) OVER (ORDER BY order_date) AS prev_amount, LEAD(amount) OVER (ORDER BY order_date) AS next_amount FROM orders;

Version Support

MySQL: Since 8.0 PostgreSQL: Since 8.4 SQL Server: Since 2012 Oracle: Since 8i SQLite: Since 3.25.0

Per-Database Syntax & Notes

MySQL

LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). Offset defaults to 1. Default value returned when offset goes out of bounds (instead of NULL).

SELECT order_date, amount, LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount FROM orders;

PostgreSQL

LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). Supports any offset and a default value for when the offset goes beyond partition bounds.

SELECT order_date, amount, LAG(amount) OVER (ORDER BY order_date) AS prev_amount, LAG(amount, 2) OVER (ORDER BY order_date) AS two_ago, LEAD(amount, 1, 0) OVER (PARTITION BY cust_id ORDER BY order_date) AS next_amount FROM orders;

SQL Server

LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). Added in SQL Server 2012. Offset must be a non-negative integer constant.

SELECT order_date, amount, LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount, LEAD(amount, 1, 0) OVER (ORDER BY order_date) AS next_amount FROM orders;

Oracle

LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). One of the earliest implementations — Oracle has had these since 8i.

SELECT order_date, amount, LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount, LEAD(amount, 1, 0) OVER (ORDER BY order_date) AS next_amount FROM orders;

SQLite

LAG() and LEAD() fully supported since SQLite 3.25.0 (September 2018).

SELECT order_date, amount, LAG(amount) OVER (ORDER BY order_date) AS prev_amount, LEAD(amount) OVER (ORDER BY order_date) AS next_amount FROM orders;