top 5 rank by category - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

top 5 rank by category

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2009-01-16 : 16:29:04

I have a table is has item, sales, category, qtysold, percentofNum

I need to find the top of item by qtysold for each individual category

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2009-01-16 : 16:41:02

[code]
select * from (select rank() over(partition by category order by qtysold desc) rnk, * from tab)t where rnk <=5[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-16 : 16:47:42

quote:

Originally posted by hanbingl


select * from (select rank() over(partition by category order by qtysold desc) rnk, * from tab)t where rnk <=5

Don't you need dense_rank() when qtysold desc is similar.


select * from (select dense_rank() over(partition by category order by qtysold desc) rnk, * from tab)t where rnk <=5

gavakie
Posting Yak Master

221 Posts

Posted - 2009-01-16 : 16:57:53

Thanks just rank worked for me.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2009-01-16 : 17:00:24

Always depend on the business rules.

Consider the top 5 ranks for grades using different ranking method.

Rank() gives
1 A
1 A
1 A
4 B
4 B
6 C
7 D
8 F

Dense_Rank():
1 A
1 A
1 A
2 B
2 B
3 C
4 D
5 F

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-16 : 17:28:14

Yep that explains.

According to BOL 2008:
Dense_rank()
If two or more rows tie for a rank in the same partition, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.

The sort order used for the whole query determines the order in which the rows appear in a result. This implies that a row ranked number one does not have to be the first row in the partition.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources