SQL ANY_VALUE Function

Summary: in this tutorial, you’ll learn how to use the SQL ANY_VALUE() aggregate function to select any value from a group.

Introduction to the SQL ANY_VALUE Aggregate Function #

In SQL, the ANY_VALUE aggregate function returns any value from a set of values.

Unlike other aggregate functions like MIN or MAX , which returns a specific value, the ANY_VALUE picks one value from a set without guaranteeing which one it returns.

In practice, you’ll find the ANY_VALUE useful in queries where the return value is irrelevant to the grouping.

Here’s the syntax of the ANY_VALUE function:

ANY_VALUE(expression)Code language: SQL (Structured Query Language) (sql)

In this syntax, the expression can be a table column or expression you want to return any value.

SQL ANY_VALUE function examples #

We’ll use the employees and departments tables from the HR sample database:

Selecting departments and any employee in each department #

The following query uses the ANY_VALUE aggregate function to return any employee in each department specified by department_id:

SELECT
  department_id,
  ANY_VALUE (first_name)
FROM
  employees
GROUP BY
  department_id
ORDER BY
  department_id;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_id | any_value
---------------+-----------
             1 | Jennifer
             2 | Michael
             3 | Shelli
             4 | Susan
             5 | Payam
             6 | David
             7 | Hermann
             8 | Jack
             9 | Steven
            10 | Luis
            11 | ShelleyCode language: SQL (Structured Query Language) (sql)

The result set includes the values from the department_id column and any value from the first_name column.

You can use the inner join clause to join the employees table with the departments table to include the department name.

SELECT
  department_name,
  ANY_VALUE (first_name) employee
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
ORDER BY
  department_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_name  | employee
------------------+----------
 Accounting       | William
 Administration   | Jennifer
 Executive        | Lex
 Finance          | Daniel
 Human Resources  | Susan
 IT               | Diana
 Marketing        | Pat
 Public Relations | Hermann
 Purchasing       | Sigal
 Sales            | Jack
 Shipping         | MatthewCode language: SQL (Structured Query Language) (sql)

Selecting employee information in high-salary groups #

The following query groups employees into high and low salaries and selects an arbitrary employee’s name for each group:

SELECT
  CASE
    WHEN salary > 10000 THEN 'High Salary'
    ELSE 'Low Salary'
  END AS salary_group,
  ANY_VALUE (first_name) AS employee
FROM
  employees
GROUP BY
  CASE
    WHEN salary > 10000 THEN 'High Salary'
    ELSE 'Low Salary'
  END;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 salary_group | employee
--------------+-----------
 High Salary  | Steven
 Low Salary   | AlexanderCode language: SQL (Structured Query Language) (sql)

Summary #

• Use the ANY_VALUE aggregate function to select a arbitrary value from a set of values..