How to select the last two records for each topic_id in MySQL

Question

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

ex: table: msg

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

I want to obtain these rows:

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

How can I do this?

thanks

Answer 1

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

Answer 2

A work around SQL not supporting the Limit followed by the IN clause is simple. Just build another subquery within your IN Clause. So for example.

SELECT a.id, a.topic_id
FROM MSG a
WHERE a.id IN (
    SELECT t.id
    FROM (Select * from MSG t
    WHERE a.topic_id = t.topic_id
    ORDER BY t.id DESC
    LIMIT 2)alias)
ORDER BY a.topic_id, a.id

Let me know how that works out for you.