MySQL Order By, Group By

Say for example you have 2 tables, one containing items and another containing item revisions and you wanted the newest revision ID for each item, if you are like me (and not many are) your first thought would be:

SELECT itemID, revisionID
FROM revisions
ORDER BY revisionID DESC
GROUP BY itemID

If you tried this like I did you will find it will throw a syntax error, as MySQL can only do GROUP BY followed by ORDER BY. I believe there are other DBMSs that so support this however. So, what do you do if you get stuck like I did? Well, you do the ORDER BY as a subquery!:

SELECT itemID, revisionID
FROM
(SELECT itemID, revisionID
FROM revisions
ORDER BY revisionID DESC)
revisions
GROUP BY itemID

This is effectively extracting the results, doing the order by and giving this result a pseudo table name, this results set is then grouped with the GROUP BY clause. You then have your ORDER BY / GROUP BY.

This is a problem I came across quite a while ago now, but thought I would share it whilst going through my own documentation