CODEWITHSUNDEEP
mysql
Shaz
Shaz

Reputation: 2647

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 GROUP BY cat LIMIT 2

Upvotes: 1

Views: 76

Answers (1)

OrangeDog
OrangeDog

Reputation: 38787

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.

Upvotes: 1

Related Questions