Блог

У меня есть таблица с такой структурой. Для каждого SID существуют разные идентификаторы элементов

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

Что мне нужно, так это удалить все, кроме последних 2 строк, с идентификаторами элементов, отсортированными по УБЫВАНИЮ.
Это тот результат, который я хочу получить:

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

Спасибо
Андреа

 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.5amp;fiddle=c6d67da817ddbb9fb890693564bcd49c

В MySQL 8 мы можем попробовать удалить соединение с помощью 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;