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
– Shef, Commented Aug 19, 2011 at 16:15

Daniel Fath · Accepted Answer · 2011-11-28 13:32:54Z

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, 2011 at 13:32

Daniel Fath

18.3k10 gold badges50 silver badges85 bronze badges

answered Aug 19, 2011 at 16:18

Nicola Cossu

56.7k15 gold badges96 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Jase Over a year ago

Thank you. It's kind of ridiculous that this works, but I'm glad it does.

Paul Lo Over a year ago

I'm really curious about the reason why MySQL cannot support the original way described by OP...it's a weird limitation.

Relm Over a year ago

I don't understand why this works as there's not explanation whatsoever.

Thai Tran Over a year ago

it is funny when the sub query does not work but the sub sub query works. Thank a ton !!!!

Peter Over a year ago

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.

|

Ertugrul Yilmaz · Accepted Answer · 2014-08-08 07:49:00Z

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 8, 2014 at 7:49

answered Aug 5, 2014 at 20:59

Ertugrul Yilmaz

1032 silver badges4 bronze badges

Comments

Samuel Kwame Antwi · Accepted Answer · 2015-01-01 14:30:42Z

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 1, 2015 at 14:30

Samuel Kwame Antwi

5955 silver badges10 bronze badges

1 Comment

Dennis98 Over a year ago

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.

Collectives™ on Stack Overflow

mySQL subquery limit

3 Answers 3

11 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related