When you group by a department's ID and also want its name, the name has the same value in every row of the group, yet SQL refuses to select it unless you add it to GROUP BY or wrap it in MAX. ANY_VALUE states the intention directly: return the value from any row of the group, because they are all the same.
Code for This Guide
The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
any_value([distinct | all] expr)
ANY_VALUE is an aggregate that returns the value of expr from one row of the group. Which row is not defined.
The Problem
Selecting a column that is neither grouped nor aggregated fails:
Example:
select d.department_id, d.department_name, count(*) as staff from departments d join employees e on e.department_id = d.department_id group by d.department_id;
Output:
Error starting at line : 1 In command - select d.department_id, d.department_name, count(*) as staff from departments d join employees e on e.department_id = d.department_id group by d.department_id Error at Command Line : 1 Column : 25 Error report - SQL Error: ORA-00979: "D"."DEPARTMENT_NAME": must appear in the GROUP BY clause or be used in an aggregate function
The Solution
ANY_VALUE around the department name makes the query valid, without adding the name to GROUP BY.
Example:
select d.department_id, any_value(d.department_name) as department, count(*) as staff from departments d join employees e on e.department_id = d.department_id group by d.department_id order by staff desc fetch first 3 rows only;
Output:
DEPARTMENT_ID DEPARTMENT STAFF
________________ ______________________ ________
20 Flight Operations 12
30 Cabin Services 11
40 Sales and Marketing 10Grouping by department_id alone is enough, because each ID has one name. ANY_VALUE is cheaper than MAX, which has to compare values, and clearer than adding a column to GROUP BY that does not change the groups.
When Not to Use It
- Use it only for values that are the same in every row of the group. If they differ, the result is an arbitrary one of them and may change between runs.
- To pick a specific row's value, such as the latest, use KEEP (DENSE_RANK LAST ORDER BY ...) instead.
- Oracle AI Database 26ai also allows GROUP BY ALL, which groups by every non-aggregated column, another way to avoid ORA-00979.
Related Guides
Conclusion
ANY_VALUE returns a value from any row of a group. Use it for columns that are functionally dependent on the GROUP BY columns, such as a name grouped by its ID, to avoid ORA-00979 without changing the grouping or paying for MAX.
