SQL query to get top "n" scores from a list

Question

I would like to find various ways to solve the real-life problem that I had: imagine that you have a contest or a game in which users collect points. You must create a query to show a list of users with the best "n" scores.

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

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

If I want to get the 3 best results, the result will be:

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

This can be implemented in the form or stored procedure as you want. My target db is Sql Server. Actually, I solved it, but I think there is another way to get the result ... faster or more efficient than mine.

Answer 1

Unconfirmed, but should work:

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

Answer 2

Here is one that works - I don’t know if it is more efficient and it is 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 “c” is to adjust the values, so you can test the second and final work of the selection — you can start with “with results like ...” if you requested an existing table.

Answer 3

@bosnic, I don’t think this will work on demand, I am not familiar with MS SQL, but I would expect it to return only 3 rows and ignore the fact that 3 users are tied to third 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 4

What about:

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

Not sure if bindings work on any other SQL Server.

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

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

Answer 5

@Espo thanks to reality check - added sub-selection to fix this.

I think the simplest answer is:

 select userid, points from users where points in (select distinct top N points from users order by points desc) 

If you want to put this in a saved process that takes N as a parameter, then you will either need to read the SQL in the variable, or execute it, or do the count trick:

 declare @SQL nvarchar(2000) set @SQL = "select userID, points from users " set @SQL = @SQL + " where points in (select distinct top " + @N set @SQL = @SQL + " points from users order by points desc)" execute @SQL 

or

 SELECT UserID, Points FROM (SELECT ROW_NUMBER() OVER (ORDER BY points DESC) AS Row, UserID, Points FROM Users) AS usersWithPoints WHERE Row between 0 and @N 

Strike> Both examples assume SQL Server and have not been tested.

Answer 6

@Rob # 37760:

 select top N points from users order by points desc 

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

Answer 7

@ Matt Hamilton

Your answer works with the example above, but will not work if the dataset is 100, 75, 75, 50, 50 (where it will only return 3 rows). TOP WITH TIES includes only the links of the last row returned ...

Answer 8

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

Answer 9

In fact, modifying WHERE IN using 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 10

try it

 select top N points from users order by points desc 

Answer 11

Hey, I found all the other answers for a long time and inefficient. My answer:

select * from users order by points desc limit 0,5

this will make the top 5 points