Jake Jake - 3 years ago 211
MySQL Question

Sql: How can i get just last two duplicate rows between more duplicate rows

My table:

id | title_en | value
--------------------------
1 | t1 | 1000
2 | t1 | 2000
3 | t1 | 3000
4 | t2 | 4000
5 | t2 | 5000
6 | t2 | 6000


I want to get the rows they have in their id:

2,3 (for title_en = t1)

and

5,6 (for title_en = t2)

this is my code, but its not working:

SELECT * FROM table GROUP BY `title_en` HAVING COUNT(`title_en`) > 2


thanks in advance.

Answer Source

This is tricky, particuarly in MySQL. I believe the following does what you want:

select t.*
from t
where t.id >= (select t2.id
               from t t2
               where t2.title_en = t.title_en
               order by t2.id desc
               limit 1
              );

MySQL is finicky about the use of limit in subqueries in the where clause. It is usually okay for a scalar subquery -- one that returns at most one row.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download