mySQL subquery limit

Question

This is probably an easy one... how can I achieve what i want with this query:

delete from posts where id not in
(SELECT id FROM posts order by timestamp desc limit 0, 15)

so, to put it in a nutshell, I want to delete every post that isn't on the latest 15.

When I try that query, I get that

MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

EDIT

mySQL Server version: 5.5.8
mySQL Client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $

Error: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

What's the MySQL version? What is the EXACT error MySQL gives? — Shef, Aug 19 '11 at 16:15

score 170 · Accepted Answer · edited Nov 28 '11 at 13:32

170

Try this:

DELETE 
FROM posts 
WHERE id not in (
      SELECT * FROM (
            SELECT id 
            FROM posts 
            ORDER BY timestamp desc limit 0, 15
      ) 
      as t);

edited Nov 28 '11 at 13:32

Daniel Fath

16,453
7
47
82

answered Aug 19 '11 at 16:18

Nicola Cossu

54,599
15
92
98

this is the answer, you're just missing a final closing parentisis – André Alçada Padez Aug 19 '11 at 16:25
you're right. I had added one parenthesis more than necessary. :) – Nicola Cossu Aug 19 '11 at 16:41
8

Thank you. It's kind of ridiculous that this works, but I'm glad it does. – Jase May 23 '13 at 14:15
This works, not sure if it's the best way. But, implemented it in project and no issues so far. Thanks. – Shiva Avula Aug 03 '13 at 10:16
5

I'm really curious about the reason why MySQL cannot support the original way described by OP...it's a weird limitation. – Paul Lo Oct 08 '14 at 10:32
@nick rulez Brilliant!! – Sunny Dec 27 '15 at 20:23
4

I don't understand why this works as there's not explanation whatsoever. – Relm Feb 17 '16 at 13:07
8

it is funny when the sub query does not work but the sub sub query works. Thank a ton !!!! – Thai Tran Aug 29 '16 at 23:03
Ty so much. This job would be so hard if there wasn't something like SO. – CodeBrauer Jun 14 '17 at 13:11
2

Just one strange detail: LIMIT in Subquery doesnt work when you test the result with IN or NOT IN. If you know you'll get only one result (so a sub query with LIMIT 1), you can do it. Just use "=" rather than "IN" and that's OK. – Peter Jan 10 '19 at 12:51
My guess is that LIMIT doesn't work specifically for the case of a subquery used by an IN clause. By moving the LIMIT into a derived table within that subquery, though, is somehow totally fine. – leedm777 Nov 07 '19 at 20:38

Ertugrul Yilmaz · Answer 2 · 2014-08-08T07:49:00.350

10

You can try this:

DELETE 
    p1.* 
FROM 
    posts p1 INNER JOIN 
    (SELECT 
            id 
    FROM 
            posts 
            ORDER BY timestamp DESC 
            LIMIT 0, 15
    ) AS p2 
ON p1.id = p2.id;

edited Aug 08 '14 at 07:49

answered Aug 05 '14 at 20:59

Ertugrul Yilmaz

103
2
4

score 2 · Answer 3 · answered Jan 01 '15 at 14:30

2

Since the newest 15 will always come from the first 15 if you order them by descending order.You can just delete any id that did not make it into the first 15. like so i just tried it and it worked fine. Hopefully it helps someone

Delete from `table` where id not in (SELECT * FROM (Select id from `table` order by id desc limit 15) as derivedTable);

answered Jan 01 '15 at 14:30

Samuel Kwame Antwi

587
5
10

That's basically the same the first answer proposed; circumventing the limitation by doing two (nested) subqueries. Also, sorting by id instead of timestamp is not reliable, as older entries with a lower id might get a newer timestamp for various reasons. – Dennis98 Mar 14 '22 at 15:59

mySQL subquery limit

3 Answers3

Linked