title: Cumulative Game Score SQL
body: <p>I have developed a game recently and the database is running on MSSQL.</p>
  
  <p>Here is my database structure</p>
  
  <p>Table : Player</p>
  
  <pre><code>PlayerID uniqueIdentifier (PK)
  PlayerName nvarchar
  </code></pre>
  
  <p>Table : GameResult</p>
  
  <pre><code>ID bigint (PK - Auto Increment)
  PlayerID uniqueIdentifier (FK)
  DateCreated Datetime
  Score int
  TimeTaken bigint
  PuzzleID int
  </code></pre>
  
  <p>I have done an SQL listing Top 50 players that sort by highest score (DESC) and timetaken (ASC)</p>
  
  <pre><code>WITH ResultSet (PlayerID, Score, TimeTaken) AS(
    SELECT DISTINCT(A.[PlayerID]), MAX(A.[Score]),MIN(A.[TimeTaken])
    FROM GameResult A
    WHERE A.[puzzleID] = @PuzzleID
    GROUP BY A.[PlayerID])
  SELECT TOP 50 RSP.[PlayerID], RSP.[PlayerName], RSA.[Score], RSA.[TimeTaken]
  FROM ResultSet RSA
  INNER JOIN Player RSP WITH(NOLOCK) ON RSA.PlayerID = RSP.PlayerID
  ORDER By RSA.[Score] DESC, RSA.[timetaken] ASC
  </code></pre>
  
  <p>However above is applicable for just 1 puzzle.</p>
  
  <p><strong>Question</strong></p>
  
  <p>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)</p>
  
  <p>2) I also need an overall score population for all the possible 1 to 7 puzzle.</p>
  
  <p>3) Each player only allowed to appear on the list once. First played and first to get highest score will be rank 1st.</p>
  
  <p>I tried using CTE with UNION but the SQL statement doesn't work.</p>
  
  <p>I hope gurus here can help me out on this. Much appreciated.</p>
  
  <p>UPDATED WITH NEW SQL</p>
  
  <p>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.</p>
  
  <pre><code>;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
  </code></pre>