Delete records not in top 15

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Delete records not in top 15

Post 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?
dhenick
Forum Newbie
Posts: 19
Joined: Tue Oct 20, 2009 10:46 am
Location: Yogyakarta, Indonesia
Contact:

Re: Delete records not in top 15

Post 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)");
 
:mrgreen:
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Delete records not in top 15

Post 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");
 
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: Delete records not in top 15

Post by Mirge »

Does:

DELETE FROM `soundoff_hof` WHERE id > 15

not work?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Delete records not in top 15

Post 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");
?
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Delete records not in top 15

Post 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");
 
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Delete records not in top 15

Post 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");
 
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Delete records not in top 15

Post 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?
jegan.aaodis
Forum Newbie
Posts: 15
Joined: Fri Oct 09, 2009 1:56 am

Re: Delete records not in top 15

Post by jegan.aaodis »

Hi

Use this query
DELETE FROM T WHERE personId IN (SELECT top 10 personId FROM T ORDER BY personId)

Thanks
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: Delete records not in top 15

Post 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..
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Delete records not in top 15

Post 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.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Delete records not in top 15

Post by cdoyle »

anyone have any other ideas on how to do this?
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Delete records not in top 15

Post 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
Post Reply