nested query on the same table

Question

do you think a query like this will create problem in the execution of my software? I need to delete the all the table, except the last 2 groups of entries, grouped by the same time of insert.

delete from tableA WHERE time not in
                (
                  SELECT time FROM
                  (select distinct time from tableA order by time desc limit 2 
                  ) AS tmptable
                );

Do you have better solution? I'm using mysql 5.5

sgeddes · Accepted Answer · 2013-07-15 23:30:18Z

2

I don't see anything wrong with your query, but I prefer using an OUTER JOIN/NULL check (plus it alleviates the need for one of the nested subqueries):

delete a
from tableA a 
  left join 
  (
      select distinct time 
      from tableA 
      order by time desc 
      limit 2 
  ) b on a.time = b.time
where b.time is null

SQL Fiddle Demo

answered Jul 15, 2013 at 23:30

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

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

Collectives™ on Stack Overflow

nested query on the same table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related