Search code examples
mysqlsql-deletemysql-5.5

Delete all rows except 2 for each SID (descending order)

I have a table with this structure. For each SID there are different ITEMID

| sid | itemid |
|---| ---------|
| 1 | 20600    |
| 1 | 20598    |
| 1 | 20597    |
| 1 | 20596    |
| 1 | 20595    |
| 1 | 20594    |
...
...
| 2 | 19600    |
| 2 | 19598    |
| 2 | 19597    |
| 2 | 19596    |
| 2 | 19595    |
| 2 | 19594    |
...
...

What I need is to delete all but the last 2 rows with the ITEMIDs sorted in DESCENDING way. This is the result I want to obtain:

| sid | itemid |
|---| ---------|
| 1 | 20600    |
| 1 | 20598    |
| 2 | 19600    |
| 2 | 19598    |

Thanks Andrea

Solution 
  • DELETE t0
FROM test t0
JOIN ( SELECT t1.sid,
              ( SELECT itemid
                FROM test t2
                WHERE t1.sid = t2.sid
                ORDER BY itemid DESC LIMIT 1,1 ) itemid
       FROM ( SELECT DISTINCT t3.sid
              FROM test t3 ) t1 ) t4 USING (sid)
WHERE t0.itemid < t4.itemid;

    https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=c6d67da817ddbb9fb890693564bcd49c