any_value aggregate function

  • Article

Applies to: check marked yes Databricks SQL check marked yes 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 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.

Returns

The result has the same type as expr.

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