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

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 3

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