MySQL “Group By” and “Order By”

by

A simple solution is to wrap the query into a subselect with the ORDER statement first and applying the GROUP BY later:

SELECT * FROM ( 
    SELECT `timestamp`, `fromEmail`, `subject`
    FROM `incomingEmails` 
    ORDER BY `timestamp` DESC
) AS tmp_table GROUP BY LOWER(`fromEmail`)

This is similar to using the join but looks much nicer.

Using non-aggregate columns in a SELECT with a GROUP BY clause is non-standard. MySQL will generally return the values of the first row it finds and discard the rest. Any ORDER BY clauses will only apply to the returned column value, not to the discarded ones.

IMPORTANT UPDATE
Selecting non-aggregate columns used to work in practice but should not be relied upon. Per the MySQL documentation “this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.”

As of 5.7.5 ONLY_FULL_GROUP_BY is enabled by default so non-aggregate columns cause query errors (ER_WRONG_FIELD_WITH_GROUP)

As @mikep points out below the solution is to use ANY_VALUE() from 5.7 and above

See
http://www.cafewebmaster.com/mysql-order-sort-group
https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

More Related Contents:

  1. SQL Query to get column values that correspond with MAX value of another column?
  2. mysql count group by having
  3. SQL select only rows with max value on a column [duplicate]
  4. Error related to only_full_group_by when executing a query in MySql
  5. SELECTING with multiple WHERE conditions on same column
  6. MySQL Query GROUP BY day / month / year
  7. MySQL order by before group by
  8. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  9. Grouping into interval of 5 minutes within a time range
  10. Retrieving the last record in each group – MySQL
  11. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  12. Using union and order by clause in mysql
  13. SQL order string as number
  14. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  15. MySQL – Control which row is returned by a group by
  16. Group mysql query by 15 min intervals
  17. MySQL order by “best match”
  18. MySQL/MariaDB – order by inside subquery
  19. MySQL Order before Group by
  20. MySQL Group By and Sum total value of other column
  21. Using Count to find the number of occurrences
  22. What does SQL clause “GROUP BY 1” mean?
  23. Using ORDER BY and GROUP BY together
  24. ORDER BY date and time BEFORE GROUP BY name in mysql
  25. GROUP BY – do not group NULL
  26. MySQL GROUP BY DateTime +/- 3 seconds
  27. Sort the rows according to the order specified in WHERE IN clause
  28. How to select data where a field has a min value in MySQL?
  29. MySQL Group By Hours
  30. ORDER BY alphabet first then follow by number

Leave a Comment