ANY_VALUE() OVER ...

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

Returns an arbitrary non-null value from the window frame, if one exists.

Syntax

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

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

Notes

• Returns any non-null value from the window frame
• Useful when you need a representative value but don't care which one
• Returns same type as input expression
• Non-deterministic: result may vary between executions

See also

• OVER clause

Examples

SELECT
    f1 := ZIP(ARRAY[1,2,3] AS id, ARRAY[30,10,20] AS v).TRANSFORM(SELECT ANY_VALUE(v) OVER (ORDER BY id ASC)).UNWRAP() -- Any non-null value in the frame (here, first in id order; v permuted so output is not v)
;