How can I delete all but the top 100 rows?

[Seb Janisz]

I am using MySQL to store data for an online high score chart for a computer game. I only want to keep the top 100 scores --- everything else can be deleted. It would be a huge waste of storage space to keep every score ever submitted when I only need the top 100, so I'm trying to figure out how to delete all but the top 100 scores. Here are the queries I came up with:


DELETE FROM normalmode_scores ORDER BY score DESC LIMIT 100,99999;

DELETE FROM normalmode_scores WHERE id IN (SELECT id FROM normalmode_scores ORDER BY score DESC LIMIT 100,99999);


Neither of these queries work. I have tried both MySQL 4.0 and MySQL 5.0. For the first one, I think it's complaining that LIMIT has two parameters, which apparently cannot be done with DELETE. For the second one, it complains that there is a LIMIT clause inside of a subquery, which apparently can't be done either.

I am at a loss for what to do next. Would anyone have any suggestions?