Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
The query you've given probably doesn't do what you want -- this is going to grab the max score, min time, min played date out of all of the results for each player; the values won't necessarily correspond to a single row in the table, which is what I assume you want. You have to use a ranking function that partitions by PlayerID to do that. –  Jon Seigel Jul 8 '13 at 16:37
    
@JonSeigel : I'm trying to grab the top 50 players whom in puzzle 1, have the highest score, uses minimal time to complete. If both people having the same highest score and having the same minimal time to complete, whomever play first will be in the first rank and follow by next. May I know how can I achieve that? I'm been trying to sort out this, but I have no clue on SQL. –  Simon Loh Jul 9 '13 at 1:55
add comment

1 Answer

up vote 5 down vote accepted

Okay, so here is the query modified to work the way you want:

DECLARE @players table
(
    PlayerID uniqueidentifier NOT NULL PRIMARY KEY,
    PlayerName nvarchar(64) NOT NULL
);

DECLARE @playerScores table
(
    ID bigint NOT NULL IDENTITY PRIMARY KEY,
    PlayerID uniqueidentifier NOT NULL,
    DateCreated datetime NOT NULL,
    Score int NOT NULL,
    TimeTaken bigint NOT NULL,
    PuzzleID int NOT NULL
);

DECLARE @puzzleId int = 0;

SELECT TOP 50
    a.PlayerID,
    p.PlayerName,
    a.Score,
    a.TimeTaken,
    a.PlayedDate
    FROM
    (
        SELECT
            ps.PlayerID,
            ps.Score,
            ps.TimeTaken,
            ps.DateCreated AS PlayedDate,
            ROW_NUMBER()
                OVER
                (
                    PARTITION BY ps.PlayerID
                    ORDER BY ps.Score DESC, ps.TimeTaken, ps.DateCreated
                ) AS RN
            FROM @playerScores ps
            WHERE ps.PuzzleID = @puzzleId
    ) a
    INNER JOIN @players p ON p.PlayerID = a.PlayerID
    WHERE a.RN = 1
    ORDER BY
        a.Score DESC,
        a.TimeTaken,
        a.PlayedDate;

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.