ANY_VALUE returns a single non-deterministic value of expr. You can use it as an aggregate function.

Use ANY_VALUE to optimize a query that has a GROUP BY clause. ANY_VALUE returns a value of an expression in a group. It is optimized to return the first value.

It ensures that there are no comparisons for any incoming row and also eliminates the necessity to specify every column as part of the GROUP BY clause. Because it does not compare values, ANY_VALUE returns a value more quickly than MIN or MAX in a GROUP BY query.


ALL, DISTINCT: These keywords are supported by ANY_VALUE although they have no effect on the result of the query.

expr: The expression can be a column, constant, bind variable, or an expression involving them.

NULL values in the expression are ignored.

Supports all of the data types, except for LONG, LOB, FILE, or COLLECTION.

If you use LONG, ORA-00997 is raised.

If you use LOB, FILE, or COLLECTION data types, ORA-00932 is raised.

ANY_VALUE follows the same rules as MIN and MAX.

Returns any value within each group based on the GROUP BY specification. Returns NULL if all rows in the group have NULL expression values.

The result of ANY_VALUE is not deterministic.


XMLType and ANYDATA are not supported.

Example 7-1 Using ANY_VALUE As an Aggregate Function

This example uses ANY_VALUE as an aggregate function in a GROUP BY query of the SH schema.

SELECT c.cust_id, ANY_VALUE(cust_last_name), SUM(amount_sold)
  FROM customers c, sales s
  WHERE s.cust_id = c.cust_id
  GROUP BY c.cust_id;

In the following result of the query, only the first eleven rows are shown.

------- -------------------------- ----------------
   6950 Sandburg                                 78
  17920 Oliver                                 3201
  66800 Case                                   2024
  37280 Edwards                                2256
 109850 Lindegreen                              757
   3910 Oddell                                  185
  84700 Marker                                164.4
  26380 Remler                                  118
  11600 Oppy                                    158
  23030 Rothrock                                533
  42780 Zanis                                   182
630 rows selected.