LEAD

​

Syntax

​

Window

lead(value [, offset [, default]]) [IGNORE NULLS | RESPECT NULLS] OVER ([PARTITION BY key] ORDER BY ordering_key)

​

Arguments

​

Returns

• Type: same SQL type as value after type conversion with default
• NULL behavior: NULL values count toward the offset and can be returned. If the offset goes beyond the group, the default is returned; the default is NULL when omitted. IGNORE NULLS skips NULL values when locating the requested row.
• Empty input: An empty input produces no rows.

​

Properties

​

Usage notes

• Offset and default must be constant expressions. They cannot vary by row.
• Negative offsets reverse the direction; zero selects the current row.
• A NULL offset behaves like 0 and selects the current row. Omitting the offset uses 1.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• Query tools may report a DECIMAL result as DOUBLE or a TIME result as INTEGER. The returned value still keeps its decimal precision and scale, or its time type. A reported type does not convert the value.
• Explicit window-frame clauses are not supported for this function.

​

Examples

​

Default offset and value

WITH t("value") AS (VALUES (1), (2), (3)) SELECT "value", lead("value") OVER (ORDER BY "value") AS result FROM t ORDER BY "value";

​

Choose an offset and a default

WITH sample(id, v) AS (VALUES (1, 10), (2, 20), (3, 30))
SELECT id, v, lead(v, 2, -1) OVER (ORDER BY id) AS result
FROM sample ORDER BY id;

​

Limitations

• Explicit window frame clauses are not supported.
• Use an integer offset. Fractional offsets are not supported.
• The default value cannot depend on a row’s contents.