MySQL DELETE all but latest X records

Question

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

How would I do something like that?

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

Answer 1

You could 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 values of id are always newer.

Answer 2

NOT IN is inefficient. You can slightly modify the first option in the previous answer by @Mithrandir to make it look 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));