Geek asks and answers

MSSQL Choosing the top 10, including columns with duplicate values

Hi, I’m trying to get the top ten contest winners using a rating. The problem is that there are two users with the same rating, for example, I get only the top 9 (there should be eleven entries if there are two top 3 points, and the rest, for example, are unique) ...

I am not sure how to handle this and would appreciate some recommendations.

Thanks Advance,

Yang

0
source share
3 answers

there should be eleven returned records if there are two top 3 ratings

Sounds like you want to use dense_rank.

This will give you all the lines that are in the top ten.

select T.Score
from (
       select Score,
              dense_rank() over(order by Score) as rn
       from YourTable
     ) T
where T.rn <= 10

SE-Data

+3
source

Top syntax allows you to bind

[ 
    TOP (expression) [PERCENT]
    [ WITH TIES ]
]

?

http://msdn.microsoft.com/en-us/library/ms189463.aspx

0

You want to do it like this:

SELECT TOP(10) WITH TIES
FirstName, LastName, score
FROM winners
ORDER BY score;

See TOP (Transact-SQL) for more information.

0
source

More articles:


All Articles