mysql - 检索多行中最新的行

Question

这个问题已经问过好几次了，但从未得到真正的答案!

我有一个如下所示的表格:

answer_id  q_id  answer  qscore_id  answer_timestamp
1          10    Male    3          1363091016
2          10    Male    3          1363091017
3          11    Male    3          1363091018
4          10    Male    3          1363091019
5          11    Male    3          1363091020
6          12    Male    3          1363091020
7          11    Male    3          1363091025

所以对于相同的问题(q_id)我有多个答案。我希望能够为每个问题仅检索一个答案，并且该答案是最新的答案。

IE。

应该返回三行，这些都是该 q_id 的最新答案:

4          10    Male    3          1363091019
6          12    Male    3          1363091020
7          11    Male    3          1363091020

任何帮助将不胜感激。

谢谢

Answer 1

使用子选择来获取最大时间戳，然后将其连接回表

SELECT a.answer_id, a.q_id, a.answer, a.qscore_id, a.answer_timestamp
FROM SomeTable a
INNER JOIN (SELECT q_id, MAX(answer_timestamp) AS answer_timestamp FROM SomeTable GROUP BY q_id) Sub1
ON a.q_id = Sub1.q_id AND a.answer_timestamp = Sub1.answer_timestamp