My knowledge of SQL pretty-much reduces to "the minimum required to get my stuff running", and I'm now in the position where I need to expand that knowledge.

Is there an easy (and hopefully quick) method to prune old records. Here's an example. Go to http://www.thecodezone.com/statomatic.php?flyman and choose "Poker Patience" from the pulldown to see my scores. Notice that the graph is about as crowded as I want it to get, which means that I need to start wiping out the oldest entries and only keep the 30 most recent.

So I need some kind of SQL statement to keep a range of numbers in a table and dump everything else.

My high score table looks like this. Nothing really surprising.

CREATE TABLE `hiscores` (
`date` int(6) unsigned NOT NULL default '0',
`id` int(32) NOT NULL default '0',
`score` int(10) unsigned NOT NULL default '0',
`seconds` int(10) unsigned NOT NULL default '0',
`recording` text NOT NULL,
`gamenum` int(11) NOT NULL default '0',
`attempt` int(11) NOT NULL default '0',
`place` smallint(5) unsigned NOT NULL default '0',
KEY `date` (`date`),
KEY `moves` (`score`),
KEY `seconds` (`seconds`),
KEY `id` (`id`),
KEY `gamenum` (`gamenum`),
KEY `attempt` (`attempt`),
KEY `place` (`place`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My dates aren't stored as standard SQL/ISO/whatever dates but as the 6-digit values I use to seed the randomer (year*365+dayofyear). It ain't standard, but it's still sort-able.

So, I need some equivalent to the following. . .

Given an 'id' field of 1 (poker patience) and an 'attempt' value of 0, get all of the results sorted by the integer field 'date' and delete everything but the 30 largest values.

Now then, doing that in PHP wouldn't be that hard. It'd just be a matter of querying the table sorted by date, checking the rows returned, then manually removing the smallest entries if there are more than 30 rows, but I was wondering if there was a way to do it entirely in SQL.

Any thoughts?