MySQL – Selecting a Column not in Group By

by

It’s true that this feature permits some ambiguous queries, and silently returns a result set with an arbitrary value picked from that column. In practice, it tends to be the value from the row within the group that is physically stored first.

These queries aren’t ambiguous if you only choose columns that are functionally dependent on the column(s) in the GROUP BY criteria. In other words, if there can be only one distinct value of the “ambiguous” column per value that defines the group, there’s no problem. This query would be illegal in Microsoft SQL Server (and ANSI SQL), even though it cannot logically result in ambiguity:

SELECT AVG(table1.col1), table1.personID, persons.col4
FROM table1 JOIN persons ON (table1.personID = persons.id)
GROUP BY table1.personID;

Also, MySQL has an SQL mode to make it behave per the standard: ONLY_FULL_GROUP_BY

FWIW, SQLite also permits these ambiguous GROUP BY clauses, but it chooses the value from the last row in the group.†

† At least in the version I tested. What it means to be arbitrary is that either MySQL or SQLite could change their implementation in the future, and have some different behavior. You should therefore not rely on the behavior staying they way it is currently in ambiguous cases like this. It’s better to rewrite your queries to be deterministic and not ambiguous. That’s why MySQL 5.7 now enables ONLY_FULL_GROUP_BY by default.

More Related Contents:

  1. Error related to only_full_group_by when executing a query in MySql
  2. MySQL Query GROUP BY day / month / year
  3. MySQL order by before group by
  4. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  5. Grouping into interval of 5 minutes within a time range
  6. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  7. How to group by week in MySQL?
  8. Select the 3 most recent records where the values of one column are distinct
  9. Is there ANY_VALUE capability for mysql 5.6?
  10. MySQL – Control which row is returned by a group by
  11. Group mysql query by 15 min intervals
  12. In SQL, how to select the top 2 rows for each group
  13. MySQL Order before Group by
  14. Returning the ‘last’ row of each ‘group by’ in MySQL
  15. MySQL Group By and Sum total value of other column
  16. Is it possible to GROUP BY multiple columns using MySQL?
  17. Using Count to find the number of occurrences
  18. Using ORDER BY and GROUP BY together
  19. ORDER BY date and time BEFORE GROUP BY name in mysql
  20. MySQL: How do I join same table multiple times?
  21. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  22. GROUP BY – do not group NULL
  23. Sum results of a few queries and then find top 5 in SQL
  24. mysql select sum group by date
  25. Select all months within given date span, including the ones with 0 values
  26. MySQL SELECT most frequent by group
  27. Select only last value using group by at mysql
  28. mysql count group by having
  29. group by range in mysql
  30. SQL query with avg and group by

Leave a Comment