AlmostThere AlmostThere - 3 months ago 19
SQL Question

Ranking by Grouping in SQL

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

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