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