How to delete the last row of a query
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 thi开发者_StackOverflow社区s?
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.
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. :)
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
精彩评论