Skip to main content

ANY_VALUE aggregate function

Returns an arbitrary value of expr from a group of rows. The determinism of this function varies depending on the execution environment.

In Spark-based engine, this function is non-deterministic. However, in Vertica, the system utilizes a MAX() operation to return the value, making the result deterministic.

Syntax

ANY_VALUE( [DISTINCT] expr )

Arguments

  • expr: Any expression of any type.

Returns

The result has the same type as expr.

Examples

-- Initial execution
SELECT ANY_VALUE(col) FROM (SELECT 1 col UNION ALL SELECT 2 UNION ALL SELECT 3);
-- Result in Spark-based engine: 2 (Non-deterministic)
-- Result in Vertica: 3 (Deterministic)

-- Subsequent executions may yield different results in Spark-based engine, 
-- but will remain consistent in Vertica.
SELECT ANY_VALUE(col) FROM (SELECT 1 col UNION ALL SELECT 2 UNION ALL SELECT 3);
-- Result in Spark-based engine: 1
-- Result in Vertica: 3