I have developed a game recently and the database is running on MSSQL.
Here is my database structure
Table : Player
PlayerID uniqueIdentifier (PK)
PlayerName nvarchar
Table : GameResult
ID bigint (PK - Auto Increment)
PlayerID uniqueIdentifier (FK)
DateCreated Datetime
Score int
TimeTaken bigint
PuzzleID int
I have done an SQL listing Top 50 players that sort by highest score (DESC) and timetaken (ASC)
Sql below allowed me to get the result for each puzzle id. I'm not sure if it is 100% but I believe it is correct.
;with ResultSet (PlayerID, maxScore, minTime, playedDate)
AS
(
SELECT TOP 50 PlayerID, MAX(score) as maxScore, MIN(timetaken) as minTime, MIN(datecreated) as playedDate
FROM gameresult
WHERE puzzleID = @PuzzleID
GROUP BY PlayerID
ORDER BY maxScore desc, minTime asc, playedDate asc
)
SELECT RSP.[PlayerID], RSP.[PlayerName], RSA.maxScore, RSA.minTime, RSA.PlayedDate
FROM ResultSet RSA
INNER JOIN Player RSP WITH(NOLOCK)
ON RSA.PlayerID = RSP.PlayerID
ORDER BY
maxScore DESC,
minTime ASC,
playedDate ASC
Question
1) I need to modify the SQL to do a cumulative rank of 3 puzzle ID. For example, Puzzle 1, 2, 3 and it should be sort by highest sum score (DESC), and sum timetaken (ASC)
2) I also need an overall score population for all the possible 1 to 7 puzzle.
3) Each player only allowed to appear on the list once. First played and first to get highest score will be rank 1st.
I tried using CTE with UNION but the SQL statement doesn't work.
I hope gurus here can help me out on this. Much appreciated.
Best Answer
Okay, so here is the query modified to work the way you want:
Having written this (note: indexes are not optimized), and looking at the other queries you're going to need to write, what I would actually recommend is to abandon this type of query entirely, and create a denormalized high-score table (rows are unique on the combination of
PlayerID
,PuzzleID
), on which to run aggregates instead.The reason why is because the
GameResult
table is going to grow huge in the database, and so it will be less and less efficient to run aggregates on it directly as time passes, and the requirements are incompatible with doing something like creating an indexed view to summarize the information.Also, if you aren't doing this already, it's highly likely you'll want to use an asynchronous process to compute the "leaderboards" periodically and cache the results, instead of computing them just-in-time. (You could do something like merge the current player's score with the cached leaderboards so the player can see themself on the leaderboards immediately if they got a high score.) See my answer here for some ideas to consider when implementing a caching mechanism.