CODEWITHSUNDEEP
mysqlwindow-functions
realkes
realkes

Reputation: 881

Creating duplicate rankings with window function in mysql

I have a table like below:

product country group value
p1 c1 g1 5
p1 c1 g2 6
p1 c2 g1 3
p1 c2 g2 22
p1 c3 g1 1
p1 c3 g2 6

I want to rank them considering the sum in value column for every product-country combination. So in this case, the updated table should be like this:

product country group value rank
p1 c1 g1 5 2
p1 c1 g2 6 2
p1 c2 g1 3 1
p1 c2 g2 22 1
p1 c3 g1 1 3
p1 c3 g2 6 3

p1-c1 combination will have the 2nd seed because 5+6 in the value column is higher than 7 (1+6) and lower than 25 (22+3). I used dense_rank() over (partition by product, country order by value) but it didn't work. How can I create the above ranking using mysql?

Thanks,

Upvotes: 1

Views: 106

Answers (1)

forpas
forpas

Reputation: 164224

First use SUM() window function in a subquery to get the total value for each product, country combination and then DENSE_RANK() to rank the totals:

select product, country, `group`, value,
       dense_rank() over (order by total desc) rnk  
from (
  select *, sum(value) over (partition by product, country) total
  from tablename
) t

See the demo.

Upvotes: 2

Related Questions