Declaration
ANY_VALUE(arg)
Description
When the sql_mode enables the ONLY_FULL_GROUP_BY SQL mode, this function is used to perform operations that are not FULL GROUP BY, suppressing the rejection of queries that violate the ONLY_FULL_GROUP_BY rule.
ANY_VALUE() returns the value of the specified column for the first row of data assigned to the same group. The return value and type of the function are the same as those of its parameter.
In the following example, if name is a non-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 values within each name group are 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 the primary key or a unique NOT NULL column, which uniquely determines city by name (this method is not applicable if NULL values are required as valid name values).
Another solution is to use ANY_VALUE() to reference city, which ignores the uncertainty of the city values within each name group and accepts the query. ANY_VALUE() is not an aggregate function; it merely suppresses non-deterministic tests.
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 approach 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
In the absence of a GROUP BY clause, it is uncertain which name value to select for the group when there is only one group, causing the query to fail. The ANY_VALUE() function can circumvent 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