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

Question

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

Answer 1

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

Answer 2

On MySQL 8+, we can try a delete join using ROW_NUMBER:

DELETE t1
FROM yourTable t1
INNER JOIN
(
    SELECT sid, itemid,
           ROW_NUMBER() OVER (PARTITION BY sid ORDER BY itemid DESC) rn
    FROM yourTable
) t2
    ON t2.sid = t1.sid AND t2.itemid = t1.itemid
WHERE
    t2.rn > 2;