Window Functions · Advanced

LAG

reading a previous row value.

This topic uses window functions or CTEs; MySQL requires version 8.0 or later.

Overview

LAG reads a preceding value in window order without a self-join and is useful for period comparisons, state changes, and gaps between events. The meaning of previous is defined entirely by ORDER BY inside OVER, so tied values need a stable secondary key. The first row returns NULL when no previous value exists unless a default is supplied.

Basic syntax

SELECT name, amount, LAG(amount) OVER (ORDER BY created_at) AS prev_amount FROM orders;

Examples

LAG example

Order records by created_at and read the preceding amount in preparation for calculating adjacent changes.

Sample table

Table：orders

orders
name	amount	created_at
Alice	1200	2026-01-03
Bob	900	2026-01-08
Cindy	1500	2026-02-02
David	700	2026-02-15

Run result

result
name	amount	prev_amount
Alice	1200	NULL
Bob	900	1200
Cindy	1500	900
David	700	1500

Common issues

Do window functions reduce row count?

No. They keep detail rows while calculating ranks, neighboring values, or running results.

Why can ranking appear unstable?

When window sort values tie, add a stable unique tie-breaker.

Does window ordering guarantee display order?

No. Add a query-level ORDER BY to guarantee the final output order.

Was this explanation clear?

← DENSE_RANK