How do you select top 10 for each unique key?

Question

How do you select top 10 for each unique key?

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

The imprecision was what troubled me. But it makes sense if you want to keep "ties". I assume that the optimizer has to do a sort under the covers either way.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 2

[font="Verdana"]I can't see how it can implement any kind of ranking without a sort.

My issue with the ranking functions is in how they are defined. I mean, if you are doing a distinct count, it's COUNT(DISTINCT ...) whereas the ranking functions the added bits come after the RANK() ...

Ah, who needs consistency in a language when you can keep the programmers on their toes?

[/font]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Shhhhhh.... 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

john.arnott SSChampion Points: 11882 More actions · Answer 4

Bruce W Cassidy (5/18/2009)
Bob Hovious (5/18/2009)
Mr. Wilbur said RANK()
rank() over (partition by s.objtype order by (s.usecounts) desc) as UCRank
[font="Verdana"]Erk. He did too!
Okay, key difference is this:
SQL Server Books Online
If two or more rows tie for a rank, each tied rows receives the same rank.
Whereas ROW_NUMBER() will give a unique number to each row within the same partition.
You will have to ask Mr. Wilbur why he used RANK() and not ROW_NUMBER() though. The way he's written it, if you have two objects in the top 10 that have the same usage count, it could actually return more than ten for that object type. If you always want 10 and only 10, then ROW_NUMBER() would probably be better.
[/font]

Yes, RANK() will give you more than 10 rows, but only if there's a tie for number 10. DENSE_RANK() will give you more than 10 rows if there are any duplicates.

I certainly recognize that a lot of the participants so far in this particular thread understand the ranking functions very well, but thought it may be helpful for others if I laid out how the three functions mentioned differ for this kind of application (the fourth "ranking" function is NTILE() and is not described here).

Take this simplified example:drop table #test_ranking_functions

create table #test_ranking_functions

(Description varchar(10)

,Value int)

insert into #test_ranking_functions

select 'Red', 2union all

select 'Blue', 4union all

select 'Green' ,5 union all

select 'Purple', 5union all

select 'Mauve', 7union all

select 'Violet', 9union all

select 'Magenta', 12 union all

select 'TimeWarp', 15

Select Row_Number() over (order by Value) as RowNbr

,Rank() over (order by Value) as RankNbr

,Dense_Rank() over (order by Value) as DenseRankNbr

,Description

,Value

from #test_ranking_functions

Which will give you this output:RowNbr RankNbr DenseRankNbr Description Value

-------------------- -------------------- -------------------- ----------- -----------

1 1 1 Red 2

2 2 2 Blue 4

3 3 3 Green 5

4 3 3 Purple 5

5 5 4 Mauve 7

6 6 5 Violet 9

7 7 6 Magenta 12

8 8 7 TimeWarp 15If you then were to look for the top three ROWS, looking at the RowNbr result, you would get the rows for Red, Blue and Green, but not Purple (or maybe Purple and not Green...). That may not be what is needed -- one post a while back had the example of finding the top ten salespersons and for that sort of problem one wouldn't want to arbitrarily ignore someone who happened to tie for tenth place. If you ask for top three by RANK, using the RankNbr result, you will get four rows back, Red, Blue, Green and Purple. If you ask for the rows in the top five rankings, using the DenseRankNbr, you'll get six rows -- Red, Blue, Green, Purple, Mauve and Violet.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Very nice little summary, John. Thank you.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills