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 

If you know that, for a given data set,  each name  value in fact uniquely determines the address  value, address  is effectively functionally dependent on name . To tell MySQL to accept the query, you can use the ANY_VALUE()  function: 

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

Alternatively, disable ONLY_FULL_GROUP_BY . 

The preceding example is quite simple, however. In particular, it is unlikely you would group on a single primary key column because every group would contain only one row. For addtional examples demonstrating functional dependence in more complex queries, see Section 12.20.4, “Detection of Functional Dependence” . 

If a query has aggregate functions and no GROUP BY  clause, it cannot have nonaggregated columns in the select list, HAVING  condition, or ORDER BY  list with ONLY_FULL_GROUP_BY  enabled: 

mysql> SELECT name, MAX(age) FROM t; ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ’mydb.t.name’; this is incompatible with sql_mode=only_full_group_by 

Without GROUP BY , there is a single group and it is nondeterministic which name  value to choose for the group. Here, too, ANY_VALUE()  can be used, if it is immaterial which name  value MySQL chooses: 

SELECT ANY_VALUE(name), MAX(age) FROM t; 

ONLY_FULL_GROUP_BY  also affects handling of queries that use DISTINCT  and ORDER BY . Consider the case of a table t  with three columns c1 , c2 , and c3  that contains these rows: 

c1 c2 c3 1 2 A 3 4 B 1 2 C 

Suppose that we execute the following query, expecting the results to be ordered by c3 : 

SELECT DISTINCT c1, c2 FROM t ORDER BY c3; 

To order the result, duplicates must be eliminated first. But to do so, should we keep the first row or the third? This arbitrary choice influences the retained value of c3 , which in turn influences ordering and makes it arbitrary as well. To prevent this problem, a query that has DISTINCT  and ORDER BY  is rejected as invalid if any ORDER BY  expression does not satisfy at least one of these conditions: 

•The expression is equal to one in the select list 

•All columns referenced by the expression and belonging to the query’s selected tables are elements of the select list 

Another MySQL extension to standard SQL permits references in the HAVING  clause to aliased expressions in the select list. For example, the following query returns name  values that occur only once in table orders : 

SELECT name, COUNT(name) FROM orders GROUP BY name 

