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..