content_image/673279.jpg
<img src='content_image/673279.jpg'>

• ANY_VALUE( arg ) 

This function is useful for GROUP BY  queries when the ONLY_FULL_GROUP_BY  SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine. The function return value and type are the same as the return value and type of its argument, but the function result is not checked for the ONLY_FULL_GROUP_BY  SQL mode. 

For example, if name  is a nonindexed column, the following query fails with ONLY_FULL_GROUP_BY enabled: 

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ’mydb.t.address’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 

The failure occurs because address  is a nonaggregated column that is neither named among GROUP BY  columns nor functionally dependent on them. As a result, the address  value for rows within each name  group is nondeterministic. There are multiple ways to cause MySQL to accept the query: 

•Alter the table to make name  a primary key or a unique NOT NULL  column. This enables MySQL to determine that address  is functionally dependent on name ; that is, address  is uniquely determined by name . (This technique is inapplicable if NULL  must be permitted as a valid name  value.) 

•Use ANY_VALUE()  to refer to address : 

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name; 

In this case, MySQL ignores the nondeterminism of address  values within each name  group and accepts the query. This may be useful if you simply do not care which value of a nonaggregated column is chosen for each group. ANY_VALUE()  is not an aggregate function, unlike functions such as SUM()  or COUNT() . It simply acts to suppress the test for nondeterminism. 

•Disable ONLY_FULL_GROUP_BY . This is equivalent to using ANY_VALUE()  with ONLY_FULL_GROUP_BY  enabled, as described in the previous item. 

ANY_VALUE()  is also useful if functional dependence exists between columns but MySQL cannot determine it. The following query is valid because age  is functionally dependent on the grouping column age-1 , but MySQL cannot tell that and rejects the query with ONLY_FULL_GROUP_BY  enabled: 

SELECT age FROM t GROUP BY age-1; 

