FIRST_VALUE() OVER ...
All functions > WINDOW FUNCTION > FIRST_VALUE() OVER ...
Returns the first value in the window frame.
Syntax
FIRST_VALUE(expr) [WITHIN (WHERE condition)] 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
Examples
FeatureQL
SELECT
-- First row in the default frame stays id=1’s v (30); order follows id, not the literal order in ARRAY[30, 10, 20]
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 matching row value; rows outside WITHIN return NULL
f2 := ZIP(
ARRAY(1, 2, 3, 4) AS id,
ARRAY(10, 20, 30, 40) AS v,
ARRAY(TRUE, FALSE, TRUE, TRUE) AS keep
).TRANSFORM(
SELECT FIRST_VALUE(v) WITHIN (WHERE keep) OVER (ORDER BY id ASC)
).UNWRAP()
;Result
| f1 ARRAY | f2 ARRAY |
|---|---|
| [30, 30, 30] | [10, null, 10, 10] |