FIRST_VALUE() OVER ...

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

Returns the first value in the window frame.

Syntax

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

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

Notes

• Returns the value from the first row of the window frame (ORDER BY inside OVER defines which physical row is first)
• With the default frame, first is among rows from the partition start through the current row
• Returns same type as input expression
• Useful for comparing the current row with the earliest row in frame order
• NULL values are preserved

See also

• OVER clause

Examples

SELECT
    f1 := ZIP(ARRAY[1, 2, 3] AS id, ARRAY[30, 10, 20] AS v).TRANSFORM(SELECT FIRST_VALUE(v) OVER (ORDER BY id ASC)).UNWRAP() -- First row in the default frame stays id=1’s v (30); order follows id, not the literal order in ARRAY[30, 10, 20]
;