MySQL Handling of 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: 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: 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: 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: Suppose that we execute the following query, expecting the results to be ordered 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: 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 : •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 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 SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name; 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 SELECT ANY_VALUE(name), MAX(age) FROM t; SELECT DISTINCT c1, c2 FROM t ORDER BY c3; c1 c2 c3 1 2 A 3 4 B 1 2 C SELECT name, COUNT(name) FROM orders GROUP BY name 2413