Ranking by Grouping in SQL

Question

I have the following data table called "animal" and I want to rank and group by clone version based on animal.

Animal Clone_Version
dog    0
dog    0
dog    1
dog    2
dog    2

I have tried using the following code but it lacks the grouping aspect I am looking for. As this is what it returns.

select 
x.animal
, x.clone_version
, row_number() over(partition by x.clone_version order by animal desc) 
from animal x

This is what the above code returns.

Animal  Clone_Version  RankGroup
dog      0              2
dog      0              1
dog      1              1
dog      2              2
dog      2              1

I want to rank these by grouping, so the output would be the following.

Animal  Clone_Version  RankGroup
dog      0              3
dog      0              3
dog      1              2
dog      2              1
dog      2              1

Do I need to utilize a select statement in the from clause or is there some way to reconfigure the row_number() over(partition by....order by...) to allow grouping?

Answer 1

You need a DENSE_RANK:

select 
x.animal
, x.clone_version
, dense_rank()
  over(partition by x.animal        -- for each animal
       order by clone_version desc) -- based on descending version
from animal x

But if your clone version is always increasing it's the same as:

select 
x.animal
, x.clone_version
, MAX(clone_version)
  over(partition by x.animal) - clone_version + 1
from animal x