views:

63

answers:

3

Hi,

I have a table containing the scores of my game

CREATE TABLE Scores
(
  PlayerName varchar(50),
  LevelId integer,
  Score integer,
  Difficulty integer
);

and I would like to always limit the number of score entries to 10 elements (for a specific level, and difficulty)

So when the score table has 10 entries (for a specific level, and difficulty) and the player has a new highscore, I would like to delete the last element (the lowest score), and insert the new highscore. How can I do this?

Thanks for help

A: 
DELETE FROM Scores
WHERE LevelID = ? AND Difficulty = ?
ORDER BY Score ASC
LIMIT 1

should delete the lowest score. But even if this is what you want, it might not be what you need. :)

Amadan
it looks fine, but why are you saying that it might not be what I need?I check it and tell you if it works
penpen
it's a syntax error with sqlite :( "SQL error: near "ORDER": syntax error"
penpen
Could be. I wrote with MySQL in mind. But all's well that ends well. @Cooper: +1.
Amadan
+1  A: 

How about...

DELETE FROM Scores S1
    WHERE Score < (SELECT MIN(Score)
                       FROM (SELECT Score
                                 FROM Scores S2
                                 WHERE S1.Level      = S2.Level      AND
                                       S1.Difficulty = S2.Difficulty
                                 ORDER BY Score DESC
                                 LIMIT 10) AS Derived);

although this won't work on every database. If also won't confine the table to ten rows if more than one score is level at the tenth position.

Later - edit to correct mistake pointed out by Andomar.

Brian Hooper
The subquery selects only aggregates, so it will return exactky one row. The `limit 10` has no effect.
Andomar
D'oh! You're quite right. I will edit it and correct.
Brian Hooper
thank you, it works ! :D
penpen
A: 
delete from Scores 
where playerName+'|'+cast(levelID as varchar)+'|'+cast(Difficulty as varchar)+'|'+cast(score as varchar) 
in( 
select playerName+'|'+cast(levelID as varchar)+'|'+cast(Difficulty as varchar)+'|'+cast(score as varchar)  from 
(Select rank() over (partition by cast(levelID as varchar)+'|'+cast(Difficulty as varchar) order by score desc) as bRank,* 
from Scores as b) as Ranks
where Ranks.bRank >= 10)

you can replace playerName+'|'+cast(levelID as varchar)+'|'+cast(Difficulty as varchar)+'|'+cast(score as varchar) with your primary key

This will give you the players in the 10th and beyond places:

select Ranks.* from 
(Select rank() over (partition by cast(levelID as varchar)+'|'+cast(Difficulty as varchar) 
order by score desc) as bRank,* 
from Scores as b) as Ranks
where Ranks.bRank >= 10 

Result:

bRank                playerName                                         LevelID     Score       Difficulty
-------------------- -------------------------------------------------- ----------- ----------- -----------
10                   Player 3                                           1           3           10
11                   Player 2                                           1           2           10
12                   Player 1                                           1           1           10
Denaem