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 deptno
Output:
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.