SQL Server query to return rank based on game results

Question

I have looked at multiple other question similarly asked on StackOverflow, but nothing seems to fit my bill. My query is slightly more complex. Essentially, I need to find the rank of the entry.

My table structure is:

TestEntry

Id, TotalTime, DateCreated

GameResult

GameId, TestEntryId, Duration, Score

QuestionResult

QuestionId, TestEntryId, Correct, UsersAnswer

The query to calculate all the scores is done via the following:

CREATE TABLE #GRS
(
TestEntryId uniqueidentifier,
Score int
)

CREATE TABLE #QRS
(
    TestEntryId uniqueidentifier,
    CorrectAnswers int
)
/* Populate temp tables with Game/Question results */
INSERT INTO #GRS
SELECT 
        TestEntryId, 
        SUM(Score) AS Score 
    FROM GameResult
    GROUP BY TestEntryId

INSERT INTO #QRS
SELECT 
        TestEntryId, 
        COUNT(*) CorrectAnswers
    FROM QuestionResult
    WHERE Correct = 1
    GROUP BY TestEntryId

SELECT 
        Id, ISNULL(GRS.Score,0) + (ISNULL(QRS.CorrectAnswers,0) * 25) AS Score
    FROM TestEntry TE
    LEFT JOIN #GRS GRS ON(GRS.TestEntryId = TE.Id)
    LEFT JOIN #QRS QRS ON(QRS.TestEntryId = TE.Id)
    WHERE TE.TotalTime > 0

Based on a specific TestEntry.Id, I need to determine the rank of that entry. Proving tricky because of the usage of temp tables, and the fact there is not a "TotalScore" in the TestEntry table, it is being dynamically calculated.

Answer 1

Unless theres a pressing need for temp tables, sack them and use common table expressions instead. Then use the RANK function to get a ranking for each id.

;WITH GRS AS
(
    SELECT 
        TestEntryId, 
        SUM(Score) AS Score 
    FROM GameResult
    GROUP BY TestEntryId
),
QRS AS
(
SELECT 
        TestEntryId, 
        COUNT(*) CorrectAnswers
    FROM QuestionResult
    WHERE Correct = 1
    GROUP BY TestEntryId
),
Scores AS
(
SELECT 
        Id, ISNULL(GRS.Score,0) + (ISNULL(QRS.CorrectAnswers,0) * 25) AS Score
    FROM TestEntry TE
    LEFT JOIN GRS ON(GRS.TestEntryId = TE.Id)
    LEFT JOIN QRS ON(QRS.TestEntryId = TE.Id)
    WHERE TE.TotalTime > 0
)
SELECT Id,Score,RANK() OVER (ORDER BY Score DESC) AS [Rank] FROM Scores