<p>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. </p>

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

<pre><code>UserId - Points
1      - 100
2      -  75
3      -  50
4      -  50
5      -  50
6      -  25
</code></pre>

<p>If I want the top 3 scores, the result will be:</p>

<pre><code>UserId - Points
1      - 100
2      -  75
3      -  50
4      -  50
5      -  50
</code></pre>

<p>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.</p>

[{'answer_id': 37750, 'author': 'Espo', 'author_id': 2257, 'author_profile': 'https://Stackoverflow.com/users/2257', 'pm_score': 5, 'selected': True, 'text': '<p>Untested, but should work:</p>\n\n<pre><code>select * from users where points in\n(select distinct top 3 points from users order by points desc)\n</code></pre>\n'}, {'answer_id': 37753, 'author': 'Tom', 'author_id': 3715, 'author_profile': 'https://Stackoverflow.com/users/3715', 'pm_score': 0, 'selected': False, 'text': "<p>@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.</p>\n\n<p>Something like this should work:</p>\n\n<pre><code>select userid, points \n   from scores \n   where points in (select top 3 points \n                       from scores \n                       order by points desc) \n   order by points desc\n</code></pre>\n"}, {'answer_id': 37760, 'author': 'Rob Allen', 'author_id': 149, 'author_profile': 'https://Stackoverflow.com/users/149', 'pm_score': 0, 'selected': False, 'text': '<p>@Espo thanks for the reality check - added the sub-select to correct for that.</p>\n\n<p>I think the easiest response is to:</p>\n\n<pre><code>select userid, points from users\nwhere points in (select distinct top N points from users order by points desc) \n</code></pre>\n\n<p>If you want to put that in a stored proc which takes N as a parameter, then you\'ll either have to do read the SQL into a variable then execute it, or do the row count trick:</p>\n\n<pre><code>declare @SQL nvarchar(2000)\nset @SQL = "select userID, points from users "\nset @SQL = @SQL + " where points in (select distinct top " + @N\nset @SQL = @SQL + " points from users order by points desc)"\n\nexecute @SQL\n</code></pre>\n\n<p><strike>or </p>\n\n<pre><code>SELECT  UserID, Points\nFROM     (SELECT  ROW_NUMBER() OVER (ORDER BY points DESC)\n         AS Row, UserID, Points FROM Users)\n        AS usersWithPoints\nWHERE  Row between 0 and @N\n</code></pre>\n\n<p></strike>\nBoth examples assume SQL Server and haven\'t been tested.</p>\n'}, {'answer_id': 37765, 'author': 'Espo', 'author_id': 2257, 'author_profile': 'https://Stackoverflow.com/users/2257', 'pm_score': 0, 'selected': False, 'text': '<p>@Rob#37760:</p>\n\n<pre><code>select top N points from users order by points desc\n</code></pre>\n\n<p>This query will only select 3 rows if N is 3, see the question. "Top 3" should return 5 rows.</p>\n'}, {'answer_id': 37781, 'author': 'Matt Hamilton', 'author_id': 615, 'author_profile': 'https://Stackoverflow.com/users/615', 'pm_score': 1, 'selected': False, 'text': '<p>How about:</p>\n\n<pre><code>select top 3 with ties points \nfrom scores\norder by points desc\n</code></pre>\n\n<p>Not sure if "with ties" works on anything other the SQL Server.</p>\n\n<p>On SQL Server 2005 and up, you can pass the "top" number as an int parameter:</p>\n\n<pre><code>select top (@n) with ties points \nfrom scores\norder by points desc\n</code></pre>\n'}, {'answer_id': 37813, 'author': 'crucible', 'author_id': 3717, 'author_profile': 'https://Stackoverflow.com/users/3717', 'pm_score': 2, 'selected': False, 'text': '<p>Here\'s one that works - I don\'t know if it\'s more efficient, and it\'s SQL Server 2005+</p>\n\n<pre><code>with scores as (\n    select 1 userid, 100 points\n    union select 2, 75\n    union select 3, 50\n    union select 4, 50\n    union select 5, 50\n    union select 6, 25\n),\nresults as (\n    select userid, points, RANK() over (order by points desc) as ranking \n    from scores\n)\nselect userid, points, ranking\nfrom results\nwhere ranking &lt;= 3\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 37904, 'author': 'Marius', 'author_id': 1008, 'author_profile': 'https://Stackoverflow.com/users/1008', 'pm_score': 0, 'selected': False, 'text': '<p>@Matt Hamilton</p>\n\n<p>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...</p>\n'}, {'answer_id': 37914, 'author': 'NakedBrunch', 'author_id': 3742, 'author_profile': 'https://Stackoverflow.com/users/3742', 'pm_score': 0, 'selected': False, 'text': '<p>Crucible got it (assuming SQL 2005 is an option).</p>\n'}, {'answer_id': 89325, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': '<p>Actually a modification to the WHERE IN, utilizing an INNER JOIN will be much faster.</p>\n\n<pre><code>SELECT \n   userid, points \nFROM users u\nINNER JOIN \n(\n   SELECT DISTINCT TOP N \n      points \n   FROM users \n   ORDER BY points DESC\n) AS p ON p.points = u.points\n</code></pre>\n'}, {'answer_id': 100867, 'author': 'kedar kamthe', 'author_id': 18709, 'author_profile': 'https://Stackoverflow.com/users/18709', 'pm_score': 0, 'selected': False, 'text': '<p>Try this</p>\n\n<pre><code>select top N points from users order by points desc\n</code></pre>\n'}, {'answer_id': 19721941, 'author': 'Krishna Gupta', 'author_id': 2443267, 'author_profile': 'https://Stackoverflow.com/users/2443267', 'pm_score': 0, 'selected': False, 'text': '<p>Hey I found all the other answers bit long and inefficient \nMy answer would be: </p>\n\n<p><code>select * from users order by points desc limit 0,5</code></p>\n\n<p>this will render top 5 points</p>\n'}]
['https://Stackoverflow.com/questions/37743', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1178/']