How to select the top score for each individual user and their details

Question

My website has a leaderboard page which should display the top scores for users, but not repeat any users more than once.

The database currently has a game table, storing the game information like their score, and a user table which stores all of the users information.

game_id     user_id    total_score
  1            1           501 
  1            1           401 
  1            2           551 
  1            2           501 

user_id   user_firstname   user_lastname
 1            John               Doe
 2            Jane               Doe

My expected output should be:

Jane Doe - 551
John Doe - 501

However so far I was only able to grab the top scores:

SELECT USERS.user_firstname, USERS.user_lastname, GAMES.total_score FROM [GAMES] INNER JOIN [USERS] ON GAMES.user_id = USERS.user_id ORDER BY [total_score] DESC

Answer 1

Use APPLY:

SELECT u.user_firstname, u.user_lastname, g.total_score
FROM users u CROSS APPLY
     (SELECT TOP (1) g.*
      FROM GAMES g
      WHERE g.user_id = u.user_id
      ORDER BY g.total_score DESC
     ) g
ORDER BY total_score DESC;