Delete all but 50 new lines

Question

I have an SQL table with news and Unix timestamps. I would like to keep only 50 latest stories. How can I write an SQL statement to delete any number of old stories?

Answer 1

I ended up using two queries since MySQL5 does not yet support LIMIT in subqueries

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

Answer 2

Blockquote

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

You select the identifiers of the data that you do not want to delete, and you delete all NOT IN these values ​​...

Answer 3

, , , - - , . , - , - . :

select count(*) from table;

do

delete from table order by timestamp limit result - 50;

-

• MySQL 5
• MySQL 5 , .

Answer 4

, 50 TRUNCATE TABLE, . 50 .

Answer 5

, , :

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

:

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

, , , - . , , IN, 50 , , () 50 - , SQL.

Answer 6

Perhaps 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)