Search code examples
sqlsql-serverpuzzle

SQL query to get the top "n" scores out of a list

I'd like to find the different ways to solve a real life problem I had: imagine to have a contest, or a game, during which the users collect points. You have to build a query to show the list of users with the best "n" scores.

I'm making an example to clarify. Let's say that this is the Users table, with the points earned:

UserId - Points
1      - 100
2      -  75
3      -  50
4      -  50
5      -  50
6      -  25

If I want the top 3 scores, the result will be:

UserId - Points
1      - 100
2      -  75
3      -  50
4      -  50
5      -  50

This can be realized in a view or a stored procedure, as you want. My target db is Sql Server. Actually I solved this, but I think there are different way to obtain the result... faster or more efficent than mine.

Solution

  • Untested, but should work:

    select * from users where points in
(select distinct top 3 points from users order by points desc)