Skip to main content

ANY_VALUE aggregate function

Returns some value of expr from a group of rows. For Databricks, this function is non-deterministic.

However, since Vertica does not properly support this function, we use MAX() instead and therefore is deterministic.

Syntax

ANY_VALUE( [DISTINCT] expr )

Arguments

  • expr: Any expression of any type.

Returns

The result has the same type as expr

Examples

> SELECT ANY_VALUE(col) FROM (SELECT 1 col UNION ALL SELECT 2 UNION ALL SELECT 3);
Databricks: 2
Vertica: 3

-- Subsequent executions may yield different results on Databricks.
-- But will always return the same result on Vertica.
> SELECT ANY_VALUE(col) FROM (SELECT 1 col UNION ALL SELECT 2 UNION ALL SELECT 3);
Databricks: 1
Vertica: 3