CODEWITHSUNDEEP
sqloracle-databaseranking
Kevin Bojarski
Kevin Bojarski

Reputation: 55

Oracle ranking columns on multiple fields

I am having some issues with ranking some columns in Oracle. I have two columns I need to rank--a group id and a date.

I want to group the table two ways:

  1. Rank the records in each GROUP_ID by DATETIME (RANK_1)
  2. Rank the GROUP_IDs by their DATETIME, GROUP_ID (RANK_2)

It should look like this:

GROUP_ID  |    DATE    |   RANK_1  |  RANK_2
----------|------------|-----------|----------
    2     |  1/1/2012  |      1    |    1
    2     |  1/2/2012  |      2    |    1
    2     |  1/4/2012  |      3    |    1   
    3     |  1/1/2012  |      1    |    2
    1     |  1/3/2012  |      1    |    3

I have been able to do the former, but have been unable to figure out the latter. 

SELECT   group_id,
         datetime,
         ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY datetime) AS rn,
         DENSE_RANK() OVER (ORDER BY group_id) AS rn2
FROM     table_1
ORDER BY group_id;

This incorrectly orders the RANK_2 field:

GROUP_ID  |    DATE    |   RANK_1  |  RANK_2
----------|------------|-----------|----------
    1     |  1/3/2012  |     1     |    1
    2     |  1/1/2012  |     1     |    2
    2     |  1/2/2012  |     2     |    2
    2     |  1/4/2012  |     3     |    2
    3     |  1/1/2012  |     1     |    3

Upvotes: 4

Views: 18908

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

Assuming you don't have an actual id column in the table, it appears that you want to do the second rank by the earliest date in each group. This will require a nested subquery:

select group_id,  datetime, rn,
       dense_rank() over (order by EarliestDate, group_id) as rn2
from (SELECT group_id,  datetime, 
             ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY datetime) AS rn, 
             min(datetime) OVER (partition by group_id) as EarliestDate
      FROM table_1
     ) t
ORDER BY group_id;

Upvotes: 3

Related Questions