Need to delete records that are NOT in my top 15

[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.

 

I'm using mySQL and adodb

 

My fields are

id

score

timestamp

 

I tried something like this, but it didn't work.

$removeold = $db->execute("DELETE FROM `soundoff_hof` WHERE `id` NOT IN (SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15");

 

anyone have any ideas how to accomplish what I need to do?

 

 

[Zane]

I believe that double quote at the end should be outside the parentheses

$removeold = $db->execute("DELETE FR.........N (SELECT.........T 15");

$removeold = $db->execute("DELETE FR.........N (SELECT.....T 15)");

[cdoyle]

You're right,  this is how I have it on my page now

 

$removeold = $db->execute("DELETE FROM `soundoff_hof` WHERE `id` NOT IN (SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15)");

 

but it's  still not deleting anything.

[Zane]

Have you tried running it as a SELECT statement first... just to see if it's actually finding something to delete.

SELECT `id` FROM `soundoff_hof` WHERE `id` NOT IN (SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15)

 

FYI It's usually best to do these kinds of checks from the mysql console btw.... or phpmyadmin.  rather than a php script.

[premiso]

Umm this query contradicts itself...usually you want some type of WHERE in the subquery to pull the data you do not want to delete. But basically you are saying DELETE all from that table where the id is not IN (ALL RECORDS), so nothing will ever be deleted.

 

Your logic is skewed, and as Zanus said, it is better to test queries in a console first before putting them in your script.

 

EDIT:

Sorry it is the end of the day, just noticed the LIMIT Please disregard that.

[cdoyle]

Have you tried running it as a SELECT statement first... just to see if it's actually finding something to delete.

SELECT `id` FROM `soundoff_hof` WHERE `id` NOT IN (SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15)

 

I gave it a try, and not sure why this isn't working.

 

if I run this query by itself

SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15

it runs fine.

but if I try and run it in the NOT IN part of my query,  I get a fetchrow error?

If I remove the DESC LIMIT 15,  then the error goes away, but not getting any results because the 2 queries are the same now.

 

[premiso]

What version is your MySQL? The subquery will only work on MySQL 4.x +.

 

 

I bet you are using MySQL 3.x and that would be why...what is the exact error you are getting if you are using MySQL 4.x + ?

[Zane]

Not tested but perhaps this might work...

Again, try it in the console first

 

SELECT `id` FROM `soundoff_hof` WHERE `id` NOT IN (SELECT TOP 15 score FROM `soundoff_hof`)

[cdoyle]

Looks like we have mySQL version 5.0.81

 

when I run this query

$test = $db->execute("SELECT `id` FROM `soundoff_hof` WHERE `id` NOT IN (SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15)");

I get this error

Fatal error: Call to a member function fetchrow() on a non-object in /home/caraudi/public_html/CAC_Mafia_Test_Site/test2.php on line 27

 

If I remove the DESC LIMIT 15  the error goes away.

 

If I run this query just by itself

SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15

the query runs OK?

[cdoyle]

Not tested but perhaps this might work...

Again, try it in the console first

 

SELECT `id` FROM `soundoff_hof` WHERE `id` NOT IN (SELECT TOP 15 `score` FROM `soundoff_hof`)

 

Just gave it a try, but it gives me the same error.

[premiso]

http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html

 

#

 

Unsupported subquery syntax:

 

ERROR 1235 (ER_NOT_SUPPORTED_YET)

SQLSTATE = 42000

Message = "This version of MySQL does not yet support

'LIMIT & IN/ALL/ANY/SOME subquery'"

 

This means that statements of the following form do not work yet:

 

SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1)

 

Looks like LIMIT is not allowed in SubQueries. Not sure what you can do here to fix it, moving to MySQL section as they may be able to provide more insight.

 

EDIT:

When you run that full query, not just the parts, in the console what is the error message you are given?

 

[cdoyle]

http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html

 

#

 

Unsupported subquery syntax:

 

ERROR 1235 (ER_NOT_SUPPORTED_YET)

SQLSTATE = 42000

Message = "This version of MySQL does not yet support

'LIMIT & IN/ALL/ANY/SOME subquery'"

 

This means that statements of the following form do not work yet:

 

SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1)

 

Looks like LIMIT is not allowed in SubQueries. Not sure what you can do here to fix it, moving to MySQL section as they may be able to provide more insight.

 

EDIT:

When you run that full query, not just the parts, in the console what is the error message you are given?

 

 

ah, well that explains it.

Hopefully there is a way to achieve what I need to do.

 

[premiso]

I've been researching and seen a few "hack" attempts at this, so here is my shot:

 

SELECT `id` FROM `soundoff_hof` WHERE `id` NOT IN ( SELECT `id` FROM (SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15) as tempTable)

 

See if that works, I think it should but yea It is a pretty nasty looking hack.

 

[cdoyle]

I've been researching and seen a few "hack" attempts at this, so here is my shot:

 

SELECT `id` FROM `soundoff_hof` WHERE `id` NOT IN ( SELECT `id` FROM (SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15) as tempTable)

 

It works!

I sure don't understand it tho lol

How does this work? 

 

I just changed it back to a delete query and the records are deleted

$test = $db->execute("delete FROM `soundoff_hof` WHERE `id` NOT IN ( SELECT `id` FROM (SELECT `id` FROM `soundoff_hof` ORDER BY `score` DESC LIMIT 15) as tempTable)");

 

 

[premiso]

I think it works in that it sort of creates a "view" on the fly which is a temporary table. And since that does not actually limit the results it works. The gist, it is creating it's own table with the results, and since it is creating a temp table of sorts the limit feature has been implemented for that and it is not really a "subquery" all though it is.

 

That is the best explanation I have, someone may have more technical terms for it etc and can explain it better, but yea. That is how it works in my head.

 

Either way, glad it worked! I am pretty good at finding work arounds for certain stuff, it is nice that there was a work around for it

 

EDIT:

Remember to click solved (bottom left hand corner) if the subject is solved to your liking.

[cdoyle]

Thanks again,  this was a big help!

I really appreciate you looking into it, and figuring it out!