brucezepplin brucezepplin - 7 months ago 13
SQL Question

How to rank with multiple paritions in DB2?

Hi if I have the following table

id code year
-- ---- ----
1 a 2000
1 b 2001
1 b 2002
1 b 2003
1 c 2007
2 a 1997
3 f 2006
3 f 2014
3 a 2015


I want to be able to rank the rows by partitioning on
id
and
code
while ordering by
year
:

id code year ranked
-- ---- ---- ------
1 a 2000 1
1 b 2001 2
1 b 2002 2
1 b 2003 2
1 c 2007 3
2 a 1997 1
3 f 2006 1
3 f 2014 1
3 a 2015 2


I have tried doing:

select distinct id, code, dense_rank() over(partition by id,code order by year) as ranked


however this has resulted in

id code year ranked
-- ---- ---- ------
1 a 2000 1
1 b 2001 2
1 b 2002 3
1 b 2003 4
1 c 2007 5
2 a 1997 1
3 f 2006 1
3 f 2014 2
3 a 2015 3


i.e. it appears to have only partitioned on
id


I have tried
rank()
and
row_number()
too but these do not give the desired table. Am I missing something? I am sure dense_rank is what I need.

Answer

I think you want dense_rank().

select id, code, year,
       dense_rank() over (partition by id order by code) as rank;
from t;

However, this doesn't quite do what you want, because the codes are ordered alphabetically and you appear to want the ordering by the minimum of the year. So, use two levels of window functions:

select id, code, year, 
       dense_rank() over (partition by id order by minyear) as rank
from (select id, code, year,
             min(year) over (partition by id, code) as minyear
      from t
     ) t;
Comments