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

Question

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.

Answer 1

Untested, but should work:

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

Answer 2

@bosnic, I don't think that will work as requested, I'm not that familiar with MS SQL but I would expect it to return only 3 rows, and ignore the fact that 3 users are tied for 3rd place.

Something like this should work:

select userid, points 
   from scores 
   where points in (select top 3 points 
                       from scores 
                       order by points desc) 
   order by points desc

Answer 3

@Rob#37760:

select top N points from users order by points desc

This query will only select 3 rows if N is 3, see the question. "Top 3" should return 5 rows.

Answer 4

How about:

select top 3 with ties points 
from scores
order by points desc

Not sure if "with ties" works on anything other the SQL Server.

On SQL Server 2005 and up, you can pass the "top" number as an int parameter:

select top (@n) with ties points 
from scores
order by points desc

Answer 5

Here's one that works - I don't know if it's more efficient, and it's SQL Server 2005+

with scores as (
    select 1 userid, 100 points
    union select 2, 75
    union select 3, 50
    union select 4, 50
    union select 5, 50
    union select 6, 25
),
results as (
    select userid, points, RANK() over (order by points desc) as ranking 
    from scores
)
select userid, points, ranking
from results
where ranking <= 3

Obviously the first "with" is to set up the values, so you can test the second with, and final select work - you could start at "with results as..." if you were querying against an existing table.

Answer 6

@Matt Hamilton

Your answer works with the example above but would not work if the data set was 100, 75, 75, 50, 50 (where it would return only 3 rows). TOP WITH TIES only includes the ties of the last row returned...

Answer 7

Crucible got it (assuming SQL 2005 is an option).

Answer 8

Actually a modification to the WHERE IN, utilizing an INNER JOIN will be much faster.

SELECT 
   userid, points 
FROM users u
INNER JOIN 
(
   SELECT DISTINCT TOP N 
      points 
   FROM users 
   ORDER BY points DESC
) AS p ON p.points = u.points

Answer 9

select top N points from users order by points desc