Top 50 – SQL

Some time ago I had to select the users from a table that reached the first 50 scores in a particular game. Not 50 users, but the users that made the first top 50 scores. Here’s my solution, for my future memory :-)

CREATE PROCEDURE [dbo].[GetTop50]
AS
DECLARE @minscore int
CREATE TABLE #top50
    (userid INT PRIMARY KEY,
     score int)

INSERT INTO #top50 (userid, score)
    SELECT top 50 userid, TotalScore 
    FROM UserList 
    order by TotalScore desc

select @minscore = min(score) from #top50

delete from #top50 where score = @minscore

INSERT INTO #top50 (userid, score)
    SELECT userid, TotalScore 
    FROM UserList where TotalScore = @minscore

select * from UserList 
    where userid in (select userid from #top50)
    order by TotalScore desc

Hope this helps

Andrea

EDIT: what about a simple one like

select userid, TotalScore
from UserList
where TotalScore in
(select distinct top 50 TotalScore from UserList order by TotalScore desc)

uff... :-)