ANY_VALUE

Returns some value of the expression from the group. The result is non-deterministic.

Syntax

Aggregate function:

ANY_VALUE( [ DISTINCT ] <expr1> )

Window function:

ANY_VALUE( [ DISTINCT ] <expr1> ) OVER ( [ PARTITION BY <expr2> ] )

Arguments

• <expr1>: The input expression.
• <expr2>: The column to partition on when used as a window function.

Returns

• Returns a value of any data type.
• If the input expression is NULL, the function returns NULL.

Usage notes

• The DISTINCT keyword can be specified but has no effect.
• NULL values are not excluded; if the expression contains NULLs, the function can return NULL.
• When used as a window function, ANY_VALUE does not support:
  • An ORDER BY clause within the OVER clause.
  • Explicit window frames.

Using ANY_VALUE with GROUP BY statements

ANY_VALUE can simplify and optimize the performance of GROUP BY statements. A common problem for many queries is that the result of a query with a GROUP BY clause can only contain expressions used in the GROUP BY clause itself, or results of aggregate functions. For example:

SELECT customer.id, customer.name, SUM(orders.value) AS total_value
FROM (
    SELECT 1 AS id, 'Alice' AS name UNION ALL
    SELECT 2, 'Bob' UNION ALL
    SELECT 3, 'Charlie'
) customer
JOIN (
    SELECT 1 AS customer_id, 100 AS value UNION ALL
    SELECT 1, 150 UNION ALL
    SELECT 2, 200 UNION ALL
    SELECT 2, 50 UNION ALL
    SELECT 3, 300
) orders
ON customer.id = orders.customer_id
GROUP BY customer.id, customer.name;

In this query, the customer.name attribute needs to be in the GROUP BY to be included in the result. This is unnecessary (for example, when customer.id is known to be unique) and makes the computation possibly more complex and slower. Another option is to use an aggregate function. For example:

SELECT customer.id, MIN(customer.name), SUM(orders.value)
FROM (
    SELECT 1 AS id, 'Alice' AS name UNION ALL
    SELECT 2, 'Bob' UNION ALL
    SELECT 3, 'Charlie'
) customer
JOIN (
    SELECT 1 AS customer_id, 100 AS value UNION ALL
    SELECT 1, 150 UNION ALL
    SELECT 2, 200 UNION ALL
    SELECT 2, 50 UNION ALL
    SELECT 3, 300
) orders
ON customer.id = orders.customer_id
GROUP BY customer.id;

This simplifies the GROUP BY clause, but still requires computing the MIN function, which incurs an extra cost.

With ANY_VALUE, you can execute the following query:

SELECT customer.id, ANY_VALUE(customer.name), SUM(orders.value)
FROM (
    SELECT 1 AS id, 'Alice' AS name UNION ALL
    SELECT 2, 'Bob' UNION ALL
    SELECT 3, 'Charlie'
) customer
JOIN (
    SELECT 1 AS customer_id, 100 AS value UNION ALL
    SELECT 1, 150 UNION ALL
    SELECT 2, 200 UNION ALL
    SELECT 2, 50 UNION ALL
    SELECT 3, 300
) orders
ON customer.id = orders.customer_id
GROUP BY customer.id;