MySQL GROUP BY behavior

by

MySQL chooses a row arbitrarily. In practice, commonly used MySQL storage engines return the values from the first row in the group, with respect to the physical storage.

create table foo (id serial primary key, category varchar(10));

insert into foo (category) values 
  ('foo'), ('foo'), ('foo'), ('bar'), ('bar'), ('bar');

select * from foo group by category;

+----+----------+
| id | category |
+----+----------+
|  4 | bar      |
|  1 | foo      |
+----+----------+

Other folks are correct that MySQL allows you to run this query even though it has arbitrary and potentially misleading results. The SQL standard, and most other RDBMS vendors, disallow this kind of ambiguous GROUP BY query. This is called the Single-Value Rule: all columns in the select-list must be explicitly part of the GROUP BY criteria, or else inside an aggregate function, e.g. COUNT(), MAX(), etc.

MySQL supports a SQL mode ONLY_FULL_GROUP_BY that makes MySQL return an error if you try to run a query that violates SQL standard semantics.

AFAIK, SQLite is the only other RDBMS that allows ambiguous columns in a grouped query. SQLite returns values from the last row in the group:

select * from foo group by category;

6|bar
3|foo

We can imagine queries that would not be ambiguous, yet still violate the SQL standard semantics.

SELECT foo.*, parent_of_foo.* 
FROM foo JOIN parent_of_foo 
  ON (foo.parent_id = parent_of_foo.parent_id) 
GROUP BY foo_id;

There’s no logical way this could produce ambiguous results. Each row in foo gets its own group, if we GROUP BY the primary key of foo. So any column from foo can have only one value in the group. Even joining to another table referenced by a foreign key in foo can have only one value per group, if the groups are defined by the primary key of foo.

MySQL and SQLite trust you to design logically unambiguous queries. Formally, every column in the select-list must be a functional dependency of the columns in the GROUP BY criteria. If you don’t adhere to this, it’s your fault. 🙂

Standard SQL is more strict and disallows some queries that could be unambiguous–probably because it would be too complex for the RDBMS to be sure in general.

More Related Contents:

  1. Group by month and year in MySQL
  2. Mysql: how to select groups having certain values?
  3. MySQL #1140 – Mixing of GROUP columns
  4. Selecting all corresponding fields using MAX and GROUP BY
  5. Error related to only_full_group_by when executing a query in MySql
  6. Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]
  7. Generating a range of numbers in MySQL
  8. How to get next/previous record in MySQL?
  9. Grouping into interval of 5 minutes within a time range
  10. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  11. Error 1046 No database Selected, how to resolve?
  12. Alter a MySQL column to be AUTO_INCREMENT
  13. Is there something wrong with joins that don’t use the JOIN keyword in SQL or MySQL?
  14. MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query
  15. single fixed table with multiple columns vs flexible abstract tables
  16. mysql, iterate through column names
  17. How to delete from select in MySQL?
  18. SQL to Entity Framework Count Group-By
  19. How to ORDER BY a SUM() in MySQL?
  20. mySQL Returning the top 5 of each category
  21. GROUP BY – do not group NULL
  22. How to Optimize Queries in a Database – The Basics
  23. MySQL: Compare differences between two tables
  24. Mysql strip time component from datetime
  25. How do I start auto increment from a specific point?
  26. SQL query to group by day
  27. MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) – Unexpected result
  28. MySql bulk load command line tool
  29. ALTER TABLE without locking the table?
  30. MySQL: Typecasting NULL to 0

Leave a Comment