any aggregate function

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 to max aggregate function, but limited to a boolean argument.

Syntax 

any(expr) [FILTER ( WHERE cond ) ]

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

Arguments

  • expr: An expression that evaluates to a numeric.

  • 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