In Oracle, any_value is a function that you can use to return any value from a table on which you are aggregating a column value.
For example, you are summing up the salary on the department number for the EMP table and you want to display one of the employee names from that department. Then, in this case, the any_value function will return the first value of the employee name from the aggregated department. Below is the syntax and example:
Any_Value Syntax
any_value(expr)
Where the expr would be the column name.
Example
In the following example, it will sum the salary department-wise and will display any employee name from that department:
select deptno, any_value(ename) any_ename, sum(sal)
from emp group by deptnoOutput:
| DEPTNO | ANY_ENAME | SUM(SAL) |
|---|---|---|
| 30 | BLAKE | 9400 |
| 10 | KING | 8750 |
| 20 | JONES | 10875 |
Usually, you can not use any other column in the group by SQL query if that column is not in the group by clause. But by using the any_value function, you have the capability to use the column and get its very first value.
