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

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开发者_如何学Go
 4 1
 6 2
 7 2
 9 3
10 3

How can I do this?

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

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.

You could

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

EDIT: As it seems that mysql does not allow (yet! it'll be possible in future releases) to use LIMIT in subqueries here's a generalized solution (with no short-cut assumptions, except that msg.id is unique per topic_id):

SELECT a.id, a.topic_id
FROM MSG a
WHERE a.id IN (
    SELECT MAX(t.id)
    FROM MSG t
    WHERE a.topic_id = t.topic_id
              ) OR
      a.id IN (
    SELECT MAX(t.id)
    FROM MSG t
    WHERE a.topic_id = t.topic_id AND 
    t.id NOT IN (
        SELECT MAX(t2.id)
        FROM MSG t2
        WHERE t.topic_id = t2.topic_id
                )
              )       
ORDER BY a.topic_id, a.id

of course this is not nice, but there you are. If assumption that ids in topic_id are ascending with no holes can be made, further improvements to the query can be made.

I would like to know better answer for this but following query will work.

SELECT * FROM msg where id in (SELECT m.id FROM msg m group by topic_id ) 
          or id in (SELECT m1.id FROM msg m1 where id not in (SELECT m2.id FROM msg m2  roup by topic_id )
group by topic_id) order by id