Outdated Version

You are viewing an older version of this section. View current production version.

ANY_VALUE

An aggregate function that arbitrarily picks one value from the group. This can include a NULL value if one is present in the group. It is a non-deterministic function (returns different results each time they are called with a specific set of input values).

Syntax

ANY_VALUE (expression);

Arguments

  • expression: any numerical or string expression

Return Type

Returns the same value and type as its argument.

Example

The following example displays the use of ANY_VALUE function with the GROUP BY statement.

SELECT * FROM Emp;
+-------------------------------------------------------------+
| Name                         | City                         |
+-------------------------------------------------------------+
| Adam                         | Chicago                      |
| Tom                          | Chicago                      |
| Neil                         | Chicago                      |
| Jack                         | Norway                       |
| Mary                         | Norway                       |
| Sam                          | Chicago                      |
+-------------------------------------------------------------+
6 rows in set (28 ms)

SELECT ANY_VALUE(Name),City FROM Emp GROUP BY City;
+-------------------------------------------------------------+
| ANY_VALUE(Name)              | City                         |
+-------------------------------------------------------------+
| Jack                         | Norway                       |
| Adam                         | Chicago                      |
+-------------------------------------------------------------+
2 rows in set (1.45 sec)