LEAD() OVER ...

All functions > WINDOW FUNCTION > LEAD() OVER ...

Returns the value from the row that leads (follows) the current row by a specified offset within the result set partition.

Syntax

LEAD(expr [, offset [, default_value]]) OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

LEAD(expr [, offset [, default_value]]) OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

Notes

• Accesses data from a subsequent row in the same result set
• First expression is the value to return
• Second expression (optional) is the offset (number of rows forward, default 1)
• Third expression (optional) is the default value if offset exceeds partition bounds
• Requires ORDER BY inside OVER so the next row is well-defined
• Useful for comparing current row with future rows

See also

• OVER clause

Examples

SELECT
    f1 := ZIP(ARRAY[1,2,3] AS id, ARRAY[30,10,20] AS v).TRANSFORM(SELECT LEAD(v, 1) OVER (ORDER BY id ASC)).UNWRAP() -- Next row value along id order (v permuted so the result is not a sorted copy of v)
;