any_value
aggregate function
Applies to: Databricks SQL Databricks Runtime 11.3 LTS and above
Returns some value of expr
for a group of rows. This function is non-deterministic.
Syntax
any_value(expr[, ignoreNull]) [FILTER ( WHERE cond ) ] [ IGNORE NULLS | RESPECT NULLS ]
This function can also be invoked as a window function using the OVER
clause.
Arguments
expr
: An expression of any type.ignoreNull
: An optional BOOLEAN literal defaulting to false. The default forignoreNull
is false.cond
: An optional boolean expression filtering the rows used for aggregation.IGNORE NULLS
orRESPECT NULLS
: WhenIGNORE NULLS
is used orignoreNull
istrue
anyexpr
value that is NULL is ignored. The default isRESPECT NULLS
.
Examples
> SELECT any_value(col) FROM VALUES (10), (5), (20) AS tab(col);
10
-- Subsequent executions may yield a different results
> SELECT any_value(col) FROM VALUES (10), (5), (20) AS tab(col);
20
> SELECT any_value(col) FROM VALUES (NULL), (5), (20) AS tab(col);
NULL
> SELECT any_value(col) IGNORE NULLS FROM VALUES (NULL), (5), (20) AS tab(col);
5