Returns a single arbitrary value from the specified column.
Syntax
ANY_VALUE(<expression>) [FILTER ([WHERE] <condition>)]
Parameters
| Parameter | Description | Supported input types |
|---|
<expression> | Any expression | Any |
<condition> | An optional boolean expression to filter rows used in aggregation | BOOL |
NULL inputs. It returns NULL only when all inputs are NULL or there are no inputs.
Return Type
Same as input type
Examples
When multiple non-NULL values are present, ANY_VALUE returns one of them. The result is non-deterministic — different executions may return different values, but NULL is never returned while non-NULL values exist:
SELECT ANY_VALUE(nickname) AS any_nickname
FROM UNNEST(ARRAY['kennethpark', NULL, 'sabrina21', 'ruthgill', 'steven70']) AS players(nickname);
| any_nickname text null |
|---|
| kennethpark |
Rows: 1Execution time: 9.08ms
ANY_VALUE returns NULL when all inputs are NULL:
SELECT ANY_VALUE(data) FROM UNNEST(ARRAY[NULL, NULL, NULL]) arr(data);
Rows: 1Execution time: 8.76ms
ANY_VALUE also returns NULL when no rows are available (for example, when filtered by a WHERE clause that matches nothing):
SELECT ANY_VALUE(data) FROM UNNEST(ARRAY[1, 2, 3]) arr(data) WHERE false;
Rows: 1Execution time: 6.00ms
