Query advice: deleting everything except for N most recent records

Hey folks,

A rather fun question for someone. What’s the most elegant way to write an SQL query that does the following:

“DELETE anything older than the last 500 records”

So if the table has 600 records, it would delete the 100 oldest and leave the newest 500 intact.

I can think of a couple of ways to do it, but they both seem a little kludgy…

Any suggestions? Thanks!

  • Ben

http://dev.mysql.com/doc/refman/5.1/en/delete.html

How about a delete with an order by and a limit?