CODEWITHSUNDEEP
mysql
501 - not implemented
501 - not implemented

Reputation: 2688

Is there an alternative to IN with LIMIT?

I want to implement a procedure which updates a "high score" or "top 10 scores" in a game.

My idea is to execute the following code:

DELETE FROM Highscore
 WHERE scoreId NOT IN
   (SELECT scoreId FROM Highscore ORDER BY value DESC LIMIT 10)

But then I get an error that the version of MySQL I'm using doesn't support IN with LIMIT.

Is there another way?

Upvotes: 1

Views: 320

Answers (5)

Vladimir Djuricic
Vladimir Djuricic

Reputation: 4623

try this.

First, create temporary table of top 10

    CREATE TEMPORARY TABLE tmp_highscores 
    (SELECT scoreId FROM Highscore ORDER BY value DESC LIMIT 10);

Then, delete those NOT IN top 10

    DELETE FROM Highscore WHERE scoreId NOT IN 
    (SELECT scoreId FROM tmp_highscores)

And, not to forget: "scoreId" should be indexed on the 'Highscore' table

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Another workaround for:

DELETE FROM Highscore
WHERE scoreId NOT IN
   (SELECT scoreId FROM Highscore ORDER BY value DESC LIMIT 10)

is to enclose the subquery with the LIMIT inside another subquery:

DELETE FROM Highscore
WHERE scoreId NOT IN
   (SELECT scoreId FROM 
      (SELECT scoreId FROM Highscore ORDER BY value DESC LIMIT 10)
      AS tmp
   )

But I think it's best not to combine nested subqueries with references, in the WHERE clause, to the same table you want to delete from. I think it's better to use JOIN:

DELETE h
FROM 
      Highscore AS h
  CROSS JOIN
      ( SELECT scoreId FROM Highscore ORDER BY value DESC LIMIT 10
      ) AS tmp
WHERE h.scoreId NOT IN (SELECT scoreId FROM tmp)

or even better, if you can make it simpler (and possibly faster with an index on value), as it is possible in your case (Note that the following will keep more than 10 rows if there are ties in the 10th place):

DELETE h
FROM 
      Highscore AS h
  CROSS JOIN
      ( SELECT value 
        FROM Highscore 
        ORDER BY value DESC 
        LIMIT 1 OFFSET 9
      ) AS tmp
WHERE h.value < tmp.value

Upvotes: 2

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324630

DELETE FROM Highscore ORDER BY value DESC LIMIT 10,5

That last 5 can be any number. If you run this every time a score is added, you can have it as 1. To allow for more margin of error, use 10.

EDIT: Sorry, apparently you can't use an offset here. In that case:

DELETE FROM Highscore WHERE value < (SELECT value FROM Highscore ORDER BY value DESC LIMIT 10,1)

If it doesn't let you do that (select from the same table as an update/delete), try:

SET @tmp = (SELECT value FROM Highscore ORDER BY value DESC LIMIT 10,1)
DELETE FROM Highscore WHERE value < @tmp

EDIT again: As pointed out in comment, causes problems if 11th value is equal to 10th. Try:

SET @id = (SELECT scoreId FROM Highscore ORDER BY value DESC, scoreId DESC LIMIT 10,1), @val = (SELECT value FROM Highscore ORDER BY value DESC LIMIT 10,1)
DELETE FROM Highscore WHERE value <= @val AND scoreId < @id

Ordering by the scoreId in the first variable ensures that when there are several with the same score, no less than 10 will be left there.

Upvotes: 3

Marcus
Marcus

Reputation: 12586

Instead of inserting new rows you could update the existing rows using UPDATE.

Another way would be to edit the select that gets the highscore. That way you keep track of all the previous highscores aswell. Using ORDER BY and LIMIT would let you do such a query.

Upvotes: 1

3Dave
3Dave

Reputation: 29051

I don't have a MySQL install to test this on, but in TSQL you could do something like

delete from HighSchore
where scoreID not in
(select top 10 scoreID from HighScore order by Value desc)

Of course,it's better to do this with an outer join instead of a subquery.

Also, in my opinion, you should never delete data like this, but just retrieve the top 10 whenever you need to.

select top 10 value
from Highscore
order by Value desc;

Upvotes: 0

Related Questions