Page 1 of 1
Delete records not in top 15
Posted: Tue Oct 20, 2009 8:31 pm
by cdoyle
Hi,
I'm creating a hall of fame page in my game and the page will only display the top 15 scores.
So I want to delete the scores that get bumped down the list.
How would I write the query to delete everything NOT in the top 15?
I've been trying different things, but nothing is working.
I thought maybe something like this, but it didn't work.
Code: Select all
$removeold = $db->execute("DELETE FROM `soundoff_hof` WHERE `id` NOT IN (SELECT `id` LIMIT 15 FROM `soundoff_hof`)");
am I close with something like this, or is there another method to do it?
Re: Delete records not in top 15
Posted: Tue Oct 20, 2009 8:42 pm
by dhenick
what type of database you used?
may be it's can be help
Code: Select all
//on mysql
$removeold = $db->execute("DELETE FROM `soundoff_hof` WHERE NOT EXIST (SELECT `id` FROM `soundoff_hof` LIMIT 15)");

Re: Delete records not in top 15
Posted: Tue Oct 20, 2009 8:52 pm
by cdoyle
sorry it's mySQL and I'm using ADODB.
I tried your example, but it didn't work.
I also forgot in my LIMIT I need to tell it which ones to look out for and not delete.
So here is what I have now
Code: Select all
$removeold = $db->execute("DELETE FROM `soundoff_hof` WHERE NOT EXIST (SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15");
Re: Delete records not in top 15
Posted: Tue Oct 20, 2009 9:11 pm
by Mirge
Does:
DELETE FROM `soundoff_hof` WHERE id > 15
not work?
Re: Delete records not in top 15
Posted: Tue Oct 20, 2009 9:35 pm
by John Cartwright
Code: Select all
$removeold = $db->execute("DELETE FROM `soundoff_hof` WHERE id NOT IN (SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15");
?
Re: Delete records not in top 15
Posted: Tue Oct 20, 2009 9:43 pm
by cdoyle
John Cartwright wrote:Code: Select all
$removeold = $db->execute("DELETE FROM `soundoff_hof` WHERE id NOT IN (SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15");
?
sorry, that was a copy/paste error.
I am trying to limit my delete query by the field 'score'
So I'm trying to delete any that are not in the top 15.
Code: Select all
$removeold = $db->execute("DELETE FROM `soundoff_hof` WHERE NOT EXIST (SELECT `score` FROM `soundoff_hof`
ORDER BY `score` DESC LIMIT 15");
Re: Delete records not in top 15
Posted: Tue Oct 20, 2009 9:43 pm
by cdoyle
John Cartwright wrote:Code: Select all
$removeold = $db->execute("DELETE FROM `soundoff_hof` WHERE id NOT IN (SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15");
?
sorry, that was a copy/paste error.
I am trying to limit my delete query by the field 'score'
So I'm trying to delete any that are not in the top 15.
Code: Select all
$removeold = $db->execute("DELETE FROM `soundoff_hof` WHERE NOT EXIST (SELECT `score` FROM `soundoff_hof`
ORDER BY `score` DESC LIMIT 15");
Re: Delete records not in top 15
Posted: Tue Oct 20, 2009 9:52 pm
by John Cartwright
cdoyle wrote:John Cartwright wrote:Code: Select all
$removeold = $db->execute("DELETE FROM `soundoff_hof` WHERE id NOT IN (SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15");
?
sorry, that was a copy/paste error.
What I meant was to try the query I posted. Did you try it?
Re: Delete records not in top 15
Posted: Wed Oct 21, 2009 12:19 am
by jegan.aaodis
Hi
Use this query
DELETE FROM T WHERE personId IN (SELECT top 10 personId FROM T ORDER BY personId)
Thanks
Re: Delete records not in top 15
Posted: Wed Oct 21, 2009 12:53 am
by Mirge
jegan.aaodis wrote:Hi
Use this query
DELETE FROM T WHERE personId IN (SELECT top 10 personId FROM T ORDER BY personId)
Thanks
I don't get it..
Re: Delete records not in top 15
Posted: Wed Oct 21, 2009 6:49 pm
by cdoyle
John Cartwright wrote:cdoyle wrote:John Cartwright wrote:Code: Select all
$removeold = $db->execute("DELETE FROM `soundoff_hof` WHERE id NOT IN (SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15");
?
sorry, that was a copy/paste error.
What I meant was to try the query I posted. Did you try it?
ah sorry, I thought you had quoted my reply.
but I did just try it, and it didn't seem to work.
Re: Delete records not in top 15
Posted: Thu Oct 22, 2009 4:19 pm
by cdoyle
anyone have any other ideas on how to do this?
Re: Delete records not in top 15
Posted: Tue Nov 03, 2009 8:24 pm
by cdoyle
I'm finally getting a chance to get back to this,
Does anyone else have any ideas on how to delete all the records that would not be in my top 15?
I've tried the methods already posted, but not having any luck