GROUP BY behavior when no aggregate functions are present in the SELECT clause

by

Read MySQL documentation on this particular point.

In a nutshell, MySQL allows omitting some columns from the GROUP BY, for performance purposes, however this works only if the omitted columns all have the same value (within a grouping), otherwise, the value returned by the query are indeed indeterminate, as properly guessed by others in this post. To be sure adding an ORDER BY clause would not re-introduce any form of deterministic behavior.

Although not at the core of the issue, this example shows how using * rather than an explicit enumeration of desired columns is often a bad idea.

Excerpt from MySQL 5.0 documentation:

When using this feature, all rows in each group should have the same values
for the columns that are omitted from the GROUP BY part. The server is free
to return any value from the group, so the results are indeterminate unless
all values are the same.

More Related Contents:

  1. How can I do a FULL OUTER JOIN in MySQL?
  2. MySQL query finding values in a comma separated string
  3. How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
  4. How do I see what character set a MySQL database / table / column is?
  5. MySQL get row position in ORDER BY
  6. generate days from date range
  7. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  8. MIN/MAX vs ORDER BY and LIMIT
  9. How can I merge two MySQL tables?
  10. MySQL unknown column in ON clause
  11. MySQL not using indexes with WHERE IN clause?
  12. SQL: Find the max record per group [duplicate]
  13. Group by month and year in MySQL
  14. calculate a sum of type time using sql
  15. MySQL: Transactions vs Locking Tables
  16. Maintaining order in MySQL “IN” query
  17. Remove duplicates using only a MySQL query?
  18. Select TOP X (or bottom) percent for numeric values in MySQL
  19. Group by date only on a Datetime column
  20. Finding the next available id in MySQL
  21. How exactly does using OR in a MySQL statement differ with/without parentheses?
  22. MySQL: How to copy rows, but change a few fields?
  23. How to renumber primary index
  24. Query that ignore the spaces
  25. Hamming distance on binary strings in SQL
  26. mysql automatically store record creation timestamp
  27. Renaming foreign-key columns in MySQL
  28. update columns values with column of another table based on condition [duplicate]
  29. In SQL how do I get the maximum value for an integer?
  30. Get the first and last date of next month in MySQL

Leave a Comment