May 25, 2010
Lets say you have a table containing comments on your blog posts or articles and you want to select the latest comment made on each article, how would you do it? This is a fairly common request.
I wanted to do this in a single query. If it was possible to sort by the column containing the maximum — the date, in my case — and then group by some identifier, I would be left with the latest row for each article in the table. Alas, MySQL does not allow sorting before grouping! Grouping first then sorting is allowed, but the rows are seemingly grouped in random order and so the latest row is lost before the sorting is applied.
So an easy solution is to do a subquery which first sorts, and then selecting from that result and grouping by some identifier, which leaves you with one row per identifier. For example:
SELECT `id`, `article_id`, `txt` FROM ( SELECT `id`, `article_id`, `txt` FROM `comments` ORDER BY `date` DESC ) AS `myalias` GROUP BY `article_id`
This will then leave you with the latest comment for each article.
Note that the alias given to the subquery results is required. You can insert
WHERE conditions as usual if needed.
Hope this helps!