mysql - 按时间戳排序不正确

Question

从下表中，我尝试选择 distinct(id) 并按 timestamp desc order 它们。但我似乎没有得到正确的结果。

"id"    "head"  "type"  "updated"   "userId"    "addedDate"
"1"     "2"     "0"     "1"         "1"         "2013-11-23 21:09:23"
"1"     "2"     "1"     "1"         "1"         "2013-11-23 21:09:40"
"2"     "2"     "0"     "1"         "1"         "2013-11-23 21:09:44"
"2"     "2"     "1"     "0"         "1"         "2013-11-23 21:09:47"

我的查询

select distinct(id) as id, addedDate from test 
where userId = 1 group by id order by addedDate desc;

当前结果

"id"    "addedDate"
"2"     "2013-11-23 21:09:44"
"1"     "2013-11-23 21:09:23" // This is wrong. 
                 //It should have been the one with 2013-11-23 21:09:40

期望的结果

"id"    "addedDate"
"2"     "2013-11-23 21:09:47" //The one that was added last
"1"     "2013-11-23 21:09:40" //The one that was added last 

Answer 1

您的查询以非常错误的方式使用了 GROUP BY。您想为每个 id 找到最大的 addedDate:

SELECT id, MAX(addedDate) AS max_addedDate 
FROM test 
WHERE userId = 1 
GROUP BY id 
ORDER BY max_addedDate DESC ;

即使您不想在 SELECT 列表中显示最大日期，您仍然可以在 ORDER BY 子句中使用它:

SELECT id
FROM test 
WHERE userId = 1 
GROUP BY id 
ORDER BY MAX(addedDate) DESC ;

---

如果您有不同的要求(正如您在评论中提到的)并且您还想显示表中的更多列(但仍然是每个 ID 具有最大日期的行)，您可以将上述查询与连接一起使用:

SELECT t.id, t.addedDate, t.type           -- whatever columns you want from `t`
FROM test AS t
  JOIN
    ( SELECT id, MAX(addedDate) AS addedDate 
      FROM test 
      WHERE userId = 1 
        AND head = 2
      GROUP BY id 
    ) AS m
    ON  m.id = t.id
    AND m.addedDate = t.addedDate
WHERE t.userId = 1 
  AND t.head = 2
ORDER BY t.addedDate DESC ;