LEAD
LEAD(expr, N, default) OVER ([PARTITION BY …] ORDER BY …)
Like LAG(), but it reads the value from the row N rows down. For the last row, it returns the default value, otherwise NULL.
Parameters
- expr – the value from the next row.
- N – optional, 1 by default.
- default – optional, the value for the last row. As with LAG, MariaDB does not accept it – portably COALESCE(LEAD(…) OVER (…), default).
Examples
Examples with the customers, products and orders tables use the sample database from the SQL basics series.
SELECT customer_id, order_date, COALESCE(LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date, id), 'none') AS next_order FROM orders WHERE customer_id IN (1, 2) ORDER BY customer_id, order_date, id;
+-------------+------------+------------+ | customer_id | order_date | next_order | +-------------+------------+------------+ | 1 | 2026-01-05 | 2026-01-05 | | 1 | 2026-01-05 | 2026-02-20 | | 1 | 2026-02-20 | none | | 2 | 2026-01-12 | 2026-03-15 | | 2 | 2026-03-15 | none | +-------------+------------+------------+
Things to watch out for
- The fallback value is converted to the type of the result – mixing a date and a string as in the example returns a string.
- Window functions are in MySQL since version 8.0 and in MariaDB since 10.2. The older MySQL 5.7 does not have them.