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:

How can I do this?

are there any shortcuts we could assume? for example in the above data your id's are ascending with no holes and for the ascending ids you also have ascending topic_id - if we can assume that it'll make query easier. another approach could use assumption that there are at least two entries for each topic_id. can we assume that? — Unreason, Apr 08 '10 at 10:36

score 5 · Answer 1 · edited Nov 16 '12 at 05:13

5

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

edited Nov 16 '12 at 05:13

Dave New

38,496
59
215
394

answered Nov 16 '12 at 04:51

Robin

51
1
1

score 3 · Answer 2 · answered May 14 '10 at 01:20

3

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.

answered May 14 '10 at 01:20

Mike Silvis

1,299
2
17
30

With this query, I get the error "Unknown column 'a.topic_id' in 'where clause'" – rayne Apr 10 '17 at 07:26

Unreason · Answer 3 · 2010-04-08T11:39:03.293

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.

it gives me following error "MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'" — Salil, Apr 08 '10 at 09:54
answer updated to compensate for mysql limitation that I was not aware of. — Unreason, Apr 08 '10 at 11:39

score 0 · Answer 4 · answered Apr 08 '10 at 10:20

0

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

answered Apr 08 '10 at 10:20

Salil

46,566
21
122
156

actually it would not (there's a typo missing g in second subquery, look for roup) but more importantly - mysql will not guarantee that the value for columns not included in group comes from the last record and is free to return any value it chooses (or in other words, if your query returns desired results, then by definition it is only by coincidence and it might stop working at any given moment) – Unreason Apr 08 '10 at 14:37

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

4 Answers4

Linked