any_value aggregate function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.2 and above

Returns any random value of expr for a group of rows.


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.


  • expr: An expression of any type.

  • ignoreNull: An optional BOOLEAN literal defaulting to false. The default for ignoreNull is false.

  • cond: An optional boolean expression filtering the rows used for aggregation.

  • IGNORE NULLS or RESPECT NULLS: When IGNORE NULLS is used or ignoreNull is true any expr value that is NULL is ignored. The default is RESPECT NULLS.


The result has the same type as expr.

This function is non-deterministic.


> SELECT any_value(col) FROM VALUES (10), (5), (20) AS tab(col);

-- Subsequent executions may yield a different results
> SELECT any_value(col) FROM VALUES (10), (5), (20) AS tab(col);

> SELECT any_value(col) FROM VALUES (NULL), (5), (20) AS tab(col);

> SELECT any_value(col) IGNORE NULLS FROM VALUES (NULL), (5), (20) AS tab(col);