ANY_VALUE

Syntax

ANY_VALUE(arg)

Description

When the sql_mode is set to ONLY_FULL_GROUP_BY, this function is used to execute operations that do not use FULL GROUP BY, thereby suppressing the rejection of ONLY_FULL_GROUP_BY values.

ANY_VALUE() selects the value of the specified column from the first row of data in the same group as the return value. The return value and type of the function are the same as those of its parameter.

In the following example, if name is not an indexed column, the GROUP BY query will fail when ONLY_FULL_GROUP_BY SQL mode is enabled. This is because city is a non-aggregated column, and the city value for each name group is uncertain.

CREATE TABLE tbl1(name VARCHAR(20),age VARCHAR(10),city VARCHAR(50));
INSERT INTO tbl1 VALUES ('Alex',20,'BeiJing'),('Jim',25,'HangZhou'),('Blair',35,'NanJing');
 SET sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected

 SELECT name, city, MAX(age) FROM tbl1 GROUP BY name;
ERROR 1055 (42000): 'test.tbl1.city' is not in GROUP BY

One solution is to modify the table to make name a primary key or a unique NOT NULL column. This way, city is uniquely determined by name (this method is not applicable if NULL is allowed as a valid name value).

Another solution is to use ANY_VALUE() to reference city. This ignores the uncertainty of city values within each name group and accepts the query. ANY_VALUE() is not an aggregate function; it simply suppresses the non-deterministic test.

SELECT name, ANY_VALUE(city), MAX(age) FROM tbl1 GROUP BY name;
+-------+-----------------+----------+
| name  | ANY_VALUE(city) | MAX(age) |
+-------+-----------------+----------+
| Alex  | BeiJing         | 20       |
| Jim   | HangZhou        | 25       |
| Blair | NanJing         | 35       |
+-------+-----------------+----------+
3 rows in set (0.002 sec)

Another method is to disable ONLY_FULL_GROUP_BY. This is equivalent to using ANY_VALUE() when ONLY_FULL_GROUP_BY is enabled, as described above.

Examples

When there is no GROUP BY clause, the database cannot determine which name value to select for the single group, so the query fails. However, ANY_VALUE() can bypass this issue and allow the database to accept queries that reference aggregate functions.

SELECT name, MAX(age) FROM tbl1;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
SELECT ANY_VALUE(name), MAX(age) FROM tbl1;
+-----------------+----------+
| ANY_VALUE(name) | MAX(age) |
+-----------------+----------+
| Alex            | 35       |
+-----------------+----------+
1 row in set (0.001 sec)