CODEWITHSUNDEEP
mysqlsql
Gilean
Gilean

Reputation: 14748

Delete all but the 50 newest rows

I have a SQL table with news stories and Unix timestamps. I'd like to only keep the 50 newest stories. How would I write an SQL statement to delete any amount of older stories?

Upvotes: 2

Views: 2698

Answers (7)

Paul Brownsea
Paul Brownsea

Reputation: 29

I've just done it like this:

DELETE FROM `table` WHERE `datetime` < (SELECT `datetime` FROM `table` ORDER BY `datetime` DESC LIMIT 49,1);

Where table is the table, datetime is a datetime field.

Upvotes: 0

Max Stewart
Max Stewart

Reputation: 3583

Well, it sort of looks like you can't do it in one query - someone correct me if I'm wrong. The only way I've ever been able to do this sort of thing is to first figure out the number of rows in the table. For Example:

select count(*) from table;

then using the result do

delete from table order by timestamp limit result - 50;

You have to do it this way for two reasons -

  1. MySQL 5 doesn't support limit in subqueries for delete
  2. MySQL 5 doesn't allow you to select in a subquery from the same table you are deleting from.

Upvotes: 3

Davide Vosti
Davide Vosti

Reputation: 2485

Blockquote

delete from table where id not in (
    select id from table 
    order by id desc 
    limit 50
)

You select the ids of the data you don't want to delete, and the you delete everything NOT IN these value...

Upvotes: 8

Gilean
Gilean

Reputation: 14748

I ended up using two queries since MySQL5 doesn't yet support LIMIT's in subqueries

SELECT unixTime FROM entries ORDER BY unixTime DESC LIMIT 49, 1;
DELETE FROM entries WHERE unixTime < $sqlResult;

Upvotes: 6

EvilTeach
EvilTeach

Reputation: 28837

If you have a lot of rows, it might be better to put the 50 rows in a temporary table then use TRUNCATE TABLE to empty the table out. Then put the 50 rows back in.

Upvotes: 2

Dan Udey
Dan Udey

Reputation: 2977

Assuming this query selects the rows you want to keep:

SELECT timestampcol FROM table ORDER BY timestampcol DESC LIMIT 49,1;

Then you could use a subquery like so:

DELETE FROM table WHERE timestampcol < ( SELECT timestampcol FROM table ORDER BY timestampcol DSEC LIMIT 49,1 )

Of course, make sure you have a backup before doing anything as potentially destructive. Note that compared to the other approaches mentioned, which use IN, this one will avoid doing 50 integer comparisons for every row to be deleted, making it (potentially) 50 times faster - assuming I got my SQL right.

Upvotes: -1

gizmo
gizmo

Reputation: 11909

Maybe not the most efficient, but this should work:

DELETE FROM _table_ 
WHERE _date_ NOT IN (SELECT _date_ FROM _table_ ORDER BY _date_ DESC LIMIT 50)

Upvotes: -2

Related Questions