Get 2 most recent items of all but from 2 different categories
I'm a newbie in mysql/sql, I have a table like this:
id | cat | datetime | ....... 10 more columns
10 | 20 | 10-12-31 00:00 | .....
64 | 12 | 10-10-25 10:00 | .....
39 | 12 | 10-11-21 08:00 | .....
21 | 20 | 10-12-30 20:00 | .....
95 | 21 | 10-09-16 00:00 | .....
the result of the query would be:
id | cat | datetime | ....... 10 more columns
10 | 20 | 10-12-31 00:00 | .....
39 | 12 | 10-11-21 08:00 | .....
I have tried this but it doesn't work (and I knew it wouldn't):
SELECT *, max(datetime) FROM table GROU开发者_JAVA百科P BY cat LIMIT 2
SELECT id, cat, datetime, ...
FROM `table`
INNER JOIN (
SELECT cat, MAX(datetime) AS mdate
FROM `table`
GROUP BY cat
ORDER BY mdate DESC
LIMIT 2
) AS t
ON `table`.cat = t.cat AND `table`.datetime = t.mdate;
There may be a more efficient way, but this will work.
精彩评论