mysql - 获取按类别分组的所有项目的最后更新时间

Question

这是我的项目表:

id (int)
category_id (int)
title (varchar 255)
updated_timestamp (int)

每个项目都有一个 category_id。假设我有这些数据:

1  |  14  |  item 1  |  1376164492
2  |  11  |  item 2  |  1376164508
3  |  12  |  item 3  |  1376164512
4  |  14  |  item 4  |  1376164532
5  |  12  |  item 5  |  1376164542
6  |  11  |  item 6  |  1376164552

我的目标是获取每个类别的最后更新时间戳。结果我需要这个:

11 = 1376164522 (greatest value for all items with category_id = 11)
12 = 1376164542 (greatest value for all items with category_id = 12)
14 = 1376164532 (greatest value for all items with category_id = 14)

这是我的 SQL:

SELECT 
    `category_id`, `updated_timestamp` 
FROM 
    `my_table` 
GROUP BY 
    `category_id` 
ORDER BY 
    `updated_timestamp` 
DESC 

但是当我运行它时，我得到的结果是这样的:

11 = 1376164508 (lowest value for all items with category_id = 11)
12 = 1376164512 (greatest value for all items with category_id = 12)
14 = 1376164492 (greatest value for all items with category_id = 14)

换句话说，DESC 不起作用。我做错了什么？

谢谢!

Answer 1

SELECT *
FROM `my_table` a
WHERE NOT EXISTS (
    SELECT 1 FROM `my_table` b
    WHERE b.`category_id` = a.`category_id`
    AND b.`updated_timestamp` > a.`updated_timestamp`
)
ORDER BY `updated_timestamp`