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

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 offset 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.