如何在MySQL中为每个topic_id选择最后两个记录 [英] How to select the last two records for each topic_id in MySQL

问题描述

我必须为每个主题选择最后两个记录.

I have to select the last two records for every topic.

例如: 表格:味精

id  |  topic_id
------------
 1  |  1
 2  |  1
 3  |  1
 4  |  1
 5  |  2
 6  |  2
 7  |  2
 8  |  3
 9  |  3
10  |  3

我想获得这些行:

 3 1
 4 1
 6 2
 7 2
 9 3
10 3

我该怎么做?

谢谢

推荐答案

SELECT max(id), max(topic_id) FROM msg
GROUP BY topic_id

UNION

SELECT max(id), max(topic_id) FROM msg
WHERE id not in (
    SELECT max(id) as id FROM msg
    GROUP BY topic_id)
GROUP BY topic_id

这篇关于如何在MySQL中为每个topic_id选择最后两个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！