ANY_VALUE() GROUP BY ...

All functions > GROUP BY > ANY_VALUE() GROUP BY ...

Returns an arbitrary value from the group.

Syntax

ANY_VALUE(expr)
 [ FILTER (WHERE condition) ]
  [ GROUP BY feature [, feature ...] ]

ANY_VALUE(expr)
 [ FILTER (WHERE condition) ]
  [ GROUP BY feature [, feature ...] ]

Notes

• Result is non-deterministic: may return different values across runs
• Useful when you need any value from a group and don't care which one
• More efficient than MIN() or MAX() when exact value doesn't matter
• Returns NULL if all values in the group are NULL
• Can be used with WHERE clause to filter before aggregation
• Can be used with GROUP BY clause for grouped aggregation

Examples

SELECT
    f1 := ZIP(ARRAY[1, 1, 1] AS value).TRANSFORM(SELECT ANY_VALUE(value)).UNWRAP_ONE() -- Any value from the group
;