any aggregate function

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns true if at least one value of expr in the group is true. The any aggregate function is synonymous with max aggregate function, but limited to a boolean argument. The function is also a synonym for bool_or aggregate function.

Syntax

any(expr) [FILTER ( WHERE cond ) ]

This function can also be invoked as a window function using the OVER clause.

Arguments

  • expr: A BOOLEAN expression.
  • cond: An optional BOOLEAN expression filtering the rows used for aggregation.

Returns

A BOOLEAN.

Examples

> SELECT any(col) FROM VALUES (true), (false), (false) AS tab(col);
 true

> SELECT any(col) FROM VALUES (NULL), (true), (false) AS tab(col);
 true

> SELECT any(col) FROM VALUES (false), (false), (NULL) AS tab(col);
 false

> SELECT any(col1) FILTER (WHERE col2 = 1)
    FROM VALUES (false, 1), (false, 2), (true, 2), (NULL, 1) AS tab(col1, col2);
 false