Goldie Goldie - 4 months ago 17
SQL Question

MySQL sorting by date with GROUP BY

My table

titles
looks like this

id |group|date |title
---+-----+--------------------+--------
1 |1 |2012-07-26 18:59:30 | Title 1
2 |1 |2012-07-26 19:01:20 | Title 2
3 |2 |2012-07-26 19:18:15 | Title 3
4 |2 |2012-07-26 20:09:28 | Title 4
5 |2 |2012-07-26 23:59:52 | Title 5


I need latest result from each group ordered by date in descending order. Something like this

id |group|date |title
---+-----+--------------------+--------
5 |2 |2012-07-26 23:59:52 | Title 5
2 |1 |2012-07-26 19:01:20 | Title 2


I tried

SELECT *
FROM `titles`
GROUP BY `group`
ORDER BY MAX( `date` ) DESC


but I'm geting first results from groups. Like this

id |group|date |title
---+-----+--------------------+--------
3 |2 |2012-07-26 18:59:30 | Title 3
1 |1 |2012-07-26 19:18:15 | Title 1


What am I doing wrong?
Is this query going to be more complicated if I use LEFT JOIN?

Answer

This page was very helpful to me; it taught me how to use self-joins to get the max/min/something-n rows per group.

In your situation, it can be applied to the effect you want like so:

SELECT * FROM
(SELECT group, MAX(date) AS date FROM titles GROUP BY group)
AS x JOIN titles USING (group, date);
Comments