MySQL: How to Perform an Order By Sort before a Group By Action

Running an order by sort on a result set before applying the group by call – It’s a pretty common problem and an unfortunate result of the way in which MySQL has chosen to implement the SQL language specification.

At the heart of it all, MySQL performs any group by operation before an order by operation, meaning that you usually won’t get quite what you’re after – for example, if you are trying to get the last received feedback for every client in your database, you’ll quickly find that you fall short if you don’t fall back on this most basic of solutions to the order by before group by dilemma: running a subquery.

A subquery is a SELECT statement within another statement, and although you take a very minor performance hit, it does solve the problem very elegantly.

Essentially what you want to do is run your original statement with the order by applied, and then on this resulting statement (technically a temporary table), you then want to run the group by operation.

And luckily for us, it is as easy to implement as what it is to understand. In practice:

SELECT * FROM (SELECT * FROM `clientfeedback` ORDER BY `timestampcaptured` DESC) GROUP BY `clientname`

Nifty.