• entries
    1212
  • comments
    1738
  • views
    1141029

SQL Question

Sign in to follow this  

65 views

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=MyISAM 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?
Sign in to follow this  


2 Comments


Recommended Comments

DELETE FROM table_name WHERE id NOT IN (SELECT TOP 30 id FROM table_name ORDER BY date)

I've never done this before, but its basically trying to do what you planned to do in PHP only in 1 SQL statement

Great Coffee Break Games By The Way!!

Share this comment


Link to comment
Eh, it may be wrong - but its a monday and I'm tired

DELETE FROM hiscores WHERE id = 1 AND attempt = 0 AND NOT EXISTS (SELECT * FROM hiscores WHERE id = 1 AND attempt = 0 ORDER BY date DESC LIMIT 30)

Sanity Check: The object is to delete all rows where id = 1 and attempt = 0 that are not the 30 most recent rows with the aforementioned qualifications.

I am not resonspible if you delete your entire database on accident by listening to me :)

Share this comment


Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now