ANY_VALUE

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`

This function ignores 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);

any_value text null
NULL

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;

any_value int null
NULL

Rows: 1Execution time: 6.00ms

Functions glossary

APPROX_COUNT_DISTINCT

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Type

Examples

​Syntax

​Parameters

​Return Type

​Examples

Syntax

Parameters

Return Type

Examples