{"id":3034,"date":"2025-01-26T05:48:10","date_gmt":"2025-01-26T12:48:10","guid":{"rendered":"https:\/\/www.sqltutorial.org\/?page_id=3034"},"modified":"2025-01-26T05:49:02","modified_gmt":"2025-01-26T12:49:02","slug":"sql-any_value","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-aggregate-functions\/sql-any_value\/","title":{"rendered":"SQL ANY_VALUE Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the SQL ANY_VALUE()<\/code> aggregate function to select any value from a group.<\/p>\n\n\n\n

Introduction to the SQL ANY_VALUE Aggregate Function #<\/a><\/h2>\n\n\n\n

In SQL, the ANY_VALUE<\/code> aggregate function returns any value from a set of values.<\/p>\n\n\n\n

Unlike other aggregate functions like MIN<\/a><\/code> or MAX<\/a><\/code> , which returns a specific value, the ANY_VALUE<\/code> picks one value from a set without guaranteeing which one it returns.<\/p>\n\n\n\n

In practice, you’ll find the ANY_VALUE<\/code> useful in queries where the return value is irrelevant to the grouping.<\/p>\n\n\n\n

Here’s the syntax of the ANY_VALUE<\/code> function:<\/p>\n\n\n

ANY_VALUE(expression)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the expression<\/code> can be a table column or expression you want to return any value.<\/p>\n\n\n\n
SQL ANY_VALUE function examples #<\/a><\/h2>\n\n\n\n
We’ll use the employees<\/code> and departments<\/code> tables from the HR sample database<\/a>:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Selecting departments and any employee in each department #<\/a><\/h3>\n\n\n\n
The following query uses the ANY_VALUE<\/code> aggregate function to return any employee in each department specified by department_id<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  department_id,\n  ANY_VALUE (first_name)\nFROM<\/span>\n  employees\nGROUP<\/span> BY<\/span>\n  department_id\nORDER<\/span> BY<\/span>\n  department_id;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 department_id | any_value\n---------------+-----------<\/span>\n             1 | Jennifer\n             2 | Michael\n             3 | Shelli\n             4 | Susan\n             5 | Payam\n             6 | David\n             7 | Hermann\n             8 | Jack\n             9 | Steven\n            10 | Luis\n            11 | Shelley<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The result set includes the values from the department_id<\/code> column and any value from the first_name<\/code> column.<\/p>\n\n\n\n
SELECT<\/span>\n  department_name,\n  ANY_VALUE (first_name) employee\nFROM<\/span>\n  employees e\n  INNER<\/span> JOIN<\/span> departments d ON<\/span> d.department_id = e.department_id\nGROUP<\/span> BY<\/span>\n  department_name\nORDER<\/span> BY<\/span>\n  department_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 department_name  | employee\n------------------+----------<\/span>\n Accounting       | William\n Administration   | Jennifer\n Executive        | Lex\n Finance          | Daniel\n Human Resources  | Susan\n IT               | Diana\n Marketing        | Pat\n Public Relations | Hermann\n Purchasing       | Sigal\n Sales            | Jack\n Shipping         | Matthew<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Selecting employee information in high-salary groups #<\/a><\/h3>\n\n\n\n
The following query groups employees into high and low salaries and selects an arbitrary employee’s name for each group:<\/p>\n\n\n
SELECT<\/span>\n  CASE<\/span>\n    WHEN<\/span> salary > 10000<\/span> THEN<\/span> 'High Salary'<\/span>\n    ELSE<\/span> 'Low Salary'<\/span>\n  END<\/span> AS<\/span> salary_group,\n  ANY_VALUE (first_name) AS<\/span> employee\nFROM<\/span>\n  employees\nGROUP<\/span> BY<\/span>\n  CASE<\/span>\n    WHEN<\/span> salary > 10000<\/span> THEN<\/span> 'High Salary'<\/span>\n    ELSE<\/span> 'Low Salary'<\/span>\n  END<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 salary_group | employee\n--------------+-----------<\/span>\n High Salary  | Steven\n Low Salary   | Alexander<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the ANY_VALUE<\/code> aggregate function to select a arbitrary value from a set of values..<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
You can use the inner join<\/a> clause to join the employees<\/code> table with the departments<\/code> table to include the department name.<\/p>\n\n\n
Try it<\/a><\/p>\n\n\n\n