SQL Server Query for Rank (RowNumber) and Groupings

Question

I have a table that has some columns: User, Category, Value

And I want to make a query that will give me a ranking, of all the users by the value, but reset for the category.

Example:

user1   CategoryA 10
user2   CategoryA 11
user3   CategoryA 9
user4   CategoryB 3
user1   CategoryB 11

the query would return:

Rank  User   Category  
1     user2   CategoryA
2     user1   CategoryA
3     user3   CategoryA
1     user1   CategoryB
2     user4   CategoryB

Any ideas?

I write the query and specify the Category, It works but then I have to write loops and its very slow.

My Current Query: SELECT Rank() over (Order by Value, User, Category) as ranks, Category, User FROM Table1 Group By User, Category, Value Order by ranks asc — bladefist, Jul 16 '09 at 19:17

score 44 · Accepted Answer · answered Jul 16 '09 at 19:24

44

Use "Partition by" in the ranking function OVER clause

SELECT
    Rank() over (Partition by Category Order by Value, User, Category) as ranks,
    Category, User
FROM 
    Table1
Group By
    User, Category, Value 
Order by
    ranks asc

answered Jul 16 '09 at 19:24

gbn

422,506
82
585
676

1,261,473 records in 33 seconds (with a table join I added that wasnt included in the question). Thanks – bladefist Jul 16 '09 at 19:30
To get the same output ordering as the question output shows, Order By Category, Ranks. Also, User returns the DB User (i.e. dbo), and must be quoted to get to the column named User. (Quoted either [user] or with quoted identifier on "user".) – Shannon Severance Jul 16 '09 at 19:33
@Shannon: I started with the query that OP added in a comment. I only wanted to demonstrate the partition by – gbn Jul 17 '09 at 04:57

Charles Bretana · Answer 2 · 2009-07-17T03:03:51.843

3

 Select User, Category,
     (Select Count(*) From Table 
      Where Category = A.Category 
         And Value <= A.Value) Rank
 From Table A
 Order By Category, Value

If Value can have duplicates, then you must decide whether you want to 'count' the dupes (equivilent to RANK) or not (equivilent to DENSE_RANK, thanx @shannon)

Ordinary Rank:

 Select User, Category,
     (Select 1 + Count(*) From Table -- "1 +" gives 1-based rank, 
      Where Category = A.Category    -- take it out to get 0-based rank
         And Value < A.Value) Rank
 From Table A
 Order By Category, Value

"Dense" Rank:

 Select User, Category,
     (Select 1 + Count(Distinct Value) -- "1 +" gives 1-based rank, 
      From Table                       -- take it out to get 0-based rank
      Where Category = A.Category    
         And Value < A.Value) Rank
 From Table A
 Order By Category, Value

edited Jul 17 '09 at 03:03

answered Jul 16 '09 at 19:21

Charles Bretana

143,358
22
150
216

Very inefficient. The fact he's using RANK implies SQL 2005 at least. – gbn Jul 16 '09 at 19:25
True. SQL 2008. I'll try all answers – bladefist Jul 16 '09 at 19:26
But a) clarity comes first, then efficiency... and b) problem is about users, so we are not talking billions of records... – Charles Bretana Jul 16 '09 at 19:26
shouldn't it be Select User, Category, (Select Count(*) From Table Where Category = A.Category And Value <= A.Value) Rank From Table A Order By Category ASC, (Select Count(*) From Table Where Category = A.Category And Value <= A.Value) DESC – Booji Boy Jul 16 '09 at 19:27
I find the solution with rank function and partition by clearer. You also get different ranks in the case of ties, versus the built in rank function. For example values of 10, 20, 20 will result in 1, 3 3 for your code versus 1, 2, 2 for built in rank. – Shannon Severance Jul 16 '09 at 19:38
@Booji Boy: no, an order by in outer query doesn't matter to the inner subquery rank calculation... @Shannon, yes, so this depends on intent... If you get a 90 on a test where 10 others scored 100, is your rank 2nd or 11th ? Either can be coded using raw SQL. I'm not familiar with SQL 2k5/2k8 Rank function as yet... Can it do both ? – Charles Bretana Jul 16 '09 at 21:35
@Charles. RANK gives 11. DENSE_RANK gives 2. HOWEVER, that is different from what I pointed out. For a row, you are counting the rows ahead, yourself (just like rank) and other rows that tie with yourself. (Unlike rank.) So say 10 100s, and 2 90s. RANK & DENSE_RANK same, but the above gives 12 as the rank. – Shannon Severance Jul 16 '09 at 21:46
@shannon, in the case where column being "ranked" can have duplicates, you must do a count(Distinct RankColumn) instead of Count(*), and then use < or <= (as appropriate) to get 0-based or 1-based rank... – Charles Bretana Jul 16 '09 at 22:41
@Shannon, so for yr example; to get rank = 11, ( 1-based ) the subquery must be (Select 1 + Count(*) From Table Where Category = A.Category And Value < A.Value) Leave out the 1+ to get 0-based rank... To get DenseRank use (Select 1 + Count(distinct Value) From Table Where Category = A.Category And Value < A.Value) – Charles Bretana Jul 16 '09 at 22:44
I've found the ordinary rank method very useful when working under HyperSQL which to my regret doesn't support yet the PARTITION BY clause under ROW_NUMBER() OVER() , so indeed quadratic performance but when you have no decent windowing function what you can do :) – Shmil The Cat Oct 11 '13 at 13:00

SQL Server Query for Rank (RowNumber) and Groupings

2 Answers2

Linked

Related