开发者

Get 2 most recent items of all but from 2 different categories

开发者 https://www.devze.com 2023-02-09 14:19 出处:网络
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 | .....

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消