Syntax
ANY_VALUE(arg)
Purpose
When sql_mode is set to ONLY_FULL_GROUP_BY, this function runs non-FULL GROUP BY operations to suppress rejection to ONLY_FULL_GROUP_BY values.
The ANY_VALUE() function selects the specified column value of the first data entry in the data group as the returned data. The returned values and return types of the function are the same as those of its arguments.
In the following example, if the name is a non-indexed column and the ONLY_FULL_GROUP_BY SQL mode is enabled, GROUP BY queries will fail because city is a non-aggregate column and the city value in each name group is uncertain.
obclient> CREATE TABLE tbl1 (name VARCHAR(20), age VARCHAR(10), city VARCHAR(50));
obclient> INSERT INTO tbl1 VALUES ('Alex',20,'BeiJing'),('Jim',25,'HangZhou'),('Blair',35,'NanJing');
obclient> SET sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected
obclient> 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 the name column become the primary key or unique NOT NULL column. In this way, city is determined only by name (this method is not applicable if NULL must be accepted as a valid name value).
Another solution is to use the ANY_VALUE() function to reference city. In this way, queries are accepted while ignoring the uncertainty of the city value in each name group. ANY_VALUE() is a non-aggregate function intended only to suppress non-deterministic testing.
obclient> 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
Another solution is to disable the ONLY_FULL_GROUP_BY SQL mode. This is equivalent to using the ANY_VALUE() function while enabling the ONLY_FULL_GROUP_BY SQL mode.
Examples
If the GROUP BY clause is absent and only one group is available, the name value of the group is uncertain, and therefore queries will fail. The ANY_VALUE() function can avoid this problem and enable the database to accept queries that reference aggregate functions.
obclient> 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
obclient> SELECT ANY_VALUE(name), MAX(age) FROM tbl1;
+-----------------+----------+
| ANY_VALUE(name) | MAX(age) |
+-----------------+----------+
| Alex | 35 |
+-----------------+----------+
1 row in set