MySQL – Control which row is returned by a group by

by

It’s called selecting the group-wise maximum of a column. Here are several different approaches for mysql.

Here’s how I would do it:

SELECT *
FROM (SELECT id, max(version_id) as version_id FROM table GROUP BY id) t1
INNER JOIN table t2 on t2.id=t1.id and t1.version_id=t2.version_id

This will be relatively efficient, though mysql will create a temporary table in memory for the subquery. I assume you already have an index on (id, version_id) for this table.

It’s a deficiency in SQL that you more or less have to use a subquery for this type of problem (semi-joins are another example).

Subqueries are not well optimized in mysql but uncorrelated subqueries aren’t so bad as long as they aren’t so enormous that they will get written to disk rather than memory. Given that in this query only has two ints the subquery could be millions of rows long before that happened but the select * subquery in your first query could suffer from this problem much sooner.

More Related Contents:

  1. MySQL order by before group by
  2. MySQL “Group By” and “Order By”
  3. Select the 3 most recent records where the values of one column are distinct
  4. MySQL Order before Group by
  5. ORDER BY date and time BEFORE GROUP BY name in mysql
  6. Why does MYSQL higher LIMIT offset slow the query down?
  7. MySQL ‘Order By’ – sorting alphanumeric correctly
  8. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  9. MySQL Orderby a number, Nulls last
  10. How does MySQL process ORDER BY and LIMIT in a query?
  11. Ordering by the order of values in a SQL IN() clause
  12. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  13. Order a MySQL table by two columns
  14. How to group by week in MySQL?
  15. GROUP_CONCAT ORDER BY
  16. Is there ANY_VALUE capability for mysql 5.6?
  17. Group mysql query by 15 min intervals
  18. MySQL: Sort GROUP_CONCAT values
  19. MySQL order by “best match”
  20. Is it possible to GROUP BY multiple columns using MySQL?
  21. Using Count to find the number of occurrences
  22. Returning query results in predefined order
  23. GROUP BY – do not group NULL
  24. Mysql delete statement with limit
  25. mysql select sum group by date
  26. Mysql: Order by like?
  27. Sort the rows according to the order specified in WHERE IN clause
  28. MySQL SELECT most frequent by group
  29. mysql count group by having
  30. ORDER BY alphabet first then follow by number

Leave a Comment