any_value ​

Selects one non-NULL value from the input rows. Use this function when any member of the group is an acceptable result.

The selected value is unspecified unless an aggregate ORDER BY determines the admission order. Use min or max when the smallest or largest value is required.

Syntax

any_value(value [ORDER BY expression])

Arguments

• value — An expression of any supported value type. NULL values are skipped.

Returned value

One original input value, with its native type preserved. Empty and all-NULL inputs return NULL.

Examples

Selecting a representative value

CREATE TABLE sessions (account TEXT, browser TEXT);
INSERT INTO sessions VALUES ('Acme', 'Firefox'), ('Acme', 'Chrome'), ('Beta', NULL);

SELECT account, any_value(browser ORDER BY browser) AS browser
FROM sessions
GROUP BY account
ORDER BY account
FORMAT csv;

"Acme","Chrome"
"Beta",\N

Preserving a native value

CREATE TABLE values_to_choose (value Date);
INSERT INTO values_to_choose VALUES (CAST('2025-01-15' AS Date)), (NULL);

SELECT any_value(value)
FROM values_to_choose
FORMAT csv;

"2025-01-15"