ANY_VALUE
Introduced or updated: v1.2.815
Aggregate function.
The ANY_VALUE() function returns an arbitrary non-NULL value from the input expression. It's used in GROUP BY queries when you need to select a column that isn't grouped or aggregated.
Alias:
ANY()returns the same result asANY_VALUE()and remains available for compatibility.
Syntax
ANY_VALUE(<expr>)
Arguments
| Arguments | Description |
|---|---|
<expr> | Any expression |
Return Type
The type of <expr>. If all values are NULL, the return value is NULL.
note
ANY_VALUE()is non-deterministic and may return different values across executions.- For predictable results, use
MIN()orMAX()instead.
Example
Sample Data:
CREATE TABLE sales (
region VARCHAR,
manager VARCHAR,
sales_amount DECIMAL(10, 2)
);
INSERT INTO sales VALUES
('North', 'Alice', 15000.00),
('North', 'Alice', 12000.00),
('South', 'Bob', 20000.00);
Problem: This query fails because manager isn't in GROUP BY:
SELECT region, manager, SUM(sales_amount) -- ❌ Error
FROM sales GROUP BY region;
Old approach: Add manager to GROUP BY, but this creates more groups than needed and hurts performance:
SELECT region, manager, SUM(sales_amount)
FROM sales GROUP BY region, manager; -- ❌ Poor performance due to extra grouping
Better solution: Use ANY_VALUE() to select the manager:
SELECT
region,
ANY_VALUE(manager) AS manager, -- ✅ Works
SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region;
Result:
| region | manager | total_sales |
|--------|---------|-------------|
| North | Alice | 27000.00 |
| South | Bob | 20000.00 |