ANY_VALUE

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 as ANY_VALUE() and remains available for compatibility.

Syntax

ANY_VALUE(<expr>)

Arguments

Return Type

The type of <expr>. If all values are NULL, the return value is NULL.

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    |