How to calculate rank with group by in sql?-CodePudding

Suppose if I have table1 as following

Category  Brand  Value     
A           A1     4
B           B1     7
C           C1     8
A           A2     3
B           B2     4
C           C2     6
A           A3     9
B           B3     10
C           C3     1
A           A4     5

Now if I want to calculate rank for each brand but grouped by category how do I go about it?

Something like

Select rank() (over value)
from table
group by category

Expected output is this:

Category      Brand Value     Rank
A               A3    9         1
A               A4    5         2
A               A1    4         3
A               A2    3         4
B               B3    10        1
B               B1    7         2
B               B2    4         3
C               C1    8         1
C               C2    6         2
C               C3    1         3

CodePudding user response：

You may add a PARTITION BY clause to your RANK() call, specifying the category as the partition.

SELECT RANK() OVER (PARTITION BY category ORDER BY value) rnk
FROM yourTable
ORDER BY category, rnk;

CodePudding user response：

Maybe you are looking for something like this. See this official documentation on DENSE_RANK for more details

select brand, category, dense_rank() over(partition by category order by value desc) as dr
from table