SQL Server Query for Rank (RowNumber) and Groupings
59,103
Solution 1
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
Solution 2
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
Related videos on Youtube
Comments
-
bladefist almost 2 years
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.
-
ZygD almost 15 yearsVery inefficient. The fact he's using RANK implies SQL 2005 at least.
-
bladefist almost 15 yearsTrue. SQL 2008. I'll try all answers
-
Charles Bretana almost 15 yearsBut a) clarity comes first, then efficiency... and b) problem is about users, so we are not talking billions of records...
-
Booji Boy almost 15 yearsshouldn'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
-
Shannon Severance almost 15 yearsI 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.
-
Charles Bretana almost 15 years@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 ?
-
Shannon Severance almost 15 years@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.
-
Charles Bretana almost 15 years@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...
-
Shmil The Cat over 10 yearsI'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 :)