MySQL DELETE everything except the last X records

Question

I have a script that runs every hour on my php site. In this script, I would like some kind of MySQL query to delete every record from the table, but said the last 50.

How would I do something like this?

// pseudo code: like this? DELETE from chat WHERE id = max (ID - 50) 

Answer 1

You can try using NOT IN:

EDIT for MySQL :

 DELETE FROM chat WHERE id NOT IN ( SELECT id FROM ( SELECT id FROM chat ORDER BY id DESC LIMIT 50 ) x ); 

This is for SQL Server :

 DELETE FROM chat WHERE id NOT IN (SELECT TOP 50 id FROM chat ORDER BY id DESC) 

Assuming higher id values ​​are always newer.

Answer 2

NOT IN ineffective. You can slightly modify the first option in @Mithrandir's previous answer so that it looks like this:

 DELETE from chat WHERE id < (SELECT id FROM (SELECT id FROM chat ORDER BY id DESC LIMIT 50) t ORDER BY id ASC LIMIT 1)); 

Answer 3

You can try something like this:

 DELETE from chat WHERE id < (SELECT max(ID)-50 FROM chat) 

This will work if your values ​​for the identifier increase with step 1. Or you can use something like:

 DELETE FROM chat WHERE id NOT IN (SELECT id FROM ( SELECT ID FROM chat ORDER BY datetime_col DESC LIMIT 50) t ) -- mysql