mysql> select * from test;;
+----+------+
| id | num |
+----+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
+----+------+
5 rows in set (0.00 sec)
mysql> update test set num = rand(12)*1000000;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select * from test;
+----+--------+
| id | num |
+----+--------+
| 1 | 157418 |
| 2 | 629671 |
| 3 | 676102 |
| 4 | 491496 |
| 5 | 429176 |
+----+--------+
5 rows in set (0.00 sec)
SET @var = '{2}{3}{5}';
SELECT
SUBSTRING
(
@var := REPLACE
(
(
SELECT @var := REPLACE(@var, concat('{', `item_id`, '}'), '') AS `ids`
FROM `table`
ORDER BY `ids` DESC
LIMIT 0,1
),
'}{',
','
),
2,
length(@var) - 2
)
SELECT *
FROM table
WHERE
item_id IN (2, 3, 5)
AND
item_id NOT IN (SELECT item_id FROM table);