Optimize a query for creating a ranking in MS SQL

问题:

I'm creating an application where users do workouts. They pass on their results via an app, and these results are stored in an SQL Server database. Results are saved in this way in a SQL Server table:

I want to write a query to create a ranking based on the best score of each user. This is what I have so far:

SELECT id, 
       workout_id, 
       level_id, 
       a.user_id, 
       total_time, 
       score, 
       datetime_added
FROM nodefit_rankings_fitness as a INNER JOIN
    (
     SELECT user_id, 
            MAX(score) AS MAXSCORE 
     FROM nodefit_rankings_fitness 
     GROUP BY user_id
    ) AS lookup
ON  lookup.user_id = a.user_id
    AND 
    lookup.MAXSCORE  =  a.score
ORDER BY score DESC, 
         datetime_added DESC

This generates this ranking:

The problem is that if a user has achieved the same maximum score a number of times, he will appear multiple times in the ranking. The query must be adjusted so that when a user has the same maximum score a few times, only the result of the last attempt (based on the datetime_added column) is displayed in the rankings.

Unfortunately, I cannot find a solution myself. Help is certainly appreciated.

回答1:

If you care about performance, you should also try a correlated subquery:

SELECT id, workout_id, level_id, a.user_id, total_time, score, datetime_added
FROM nodefit_rankings_fitness nrf
WHERE nrf.id = (SELECT TOP (1) nrf2.id
                FROM nodefit_rankings_fitness nrf2
                WHERE nrf2.user_id = nrf.user_id
                ORDER BY nrf2.score DESC
               )
ORDER BY score DESC, datetime_added DESC;

In particular, this can take advantage of an index on nodefit_rankings_fitness(user_id, score desc, id).

回答2:

Window functions make stuff like this easy. Something like:

SELECT id, workout_id, level_id, user_id, total_time, score, datetime_added
FROM (SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY score DESC, datetime_added DESC) AS rn
      FROM nodefit_rankings_fitness) AS a
WHERE rn = 1
ORDER BY score DESC, datetime_added DESC;