Syntax
ANY_VALUE(arg)
Purpose
When the sql_mode is set to ONLY_FULL_GROUP_BY, this function is used to perform 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 data. 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, when the ONLY_FULL_GROUP_BY SQL mode is enabled, the GROUP BY query will fail. This is because city is a non-aggregated column, and therefore, 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 so that name becomes a primary key or a unique NOT NULL column, which uniquely determines city based on name (this method is not applicable if NULL values are allowed as valid name values).
Another solution is to use ANY_VALUE() to reference city, which ignores the uncertainty of the city value within each name group and accepts the query. ANY_VALUE() is not an aggregate function; it simply suppresses the indeterminacy check.
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
A third 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, it is uncertain which name value to select for the single group, so the query will fail. However, ANY_VALUE() can bypass this issue and allow 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