CODEWITHSUNDEEP
sqloracle-databasedense-rank
SASPYTHON
SASPYTHON

Reputation: 1621

dense_RANK() function, how can I fix? oracle

My code is like this right now

select c.id_number
      ,dense_RANK() OVER (Partition by c.id_number ORDER BY c.id_number) "rank"
from  children c

now it is showing like this

id_Number ___ rank
001           1
002           1
003           1
003           1
004           1
004           1
004           1

BUT I would like to show like this,

id_Number ___ rank
001           1
002           1
003           1
003           2
004           1
004           2
004           3

I try like this

 select c.id_number
          ,RANK() OVER (Partition by c.id_number ORDER BY c.id_number) "rank"
 from  children c

but it did not work what I want,

what is the best way to code like what I want

Upvotes: 0

Views: 1525

Answers (2)

RelaxedArcher
RelaxedArcher

Reputation: 96

Dense_Rank will assign a rank number. In case of equality, it will assign the same number for all the equal rows and then move to the next number (that's where "dense" comes from). In your example 003 is the first one from its partition, so it will have the rank of 1. The next 003 will not be the first from its partition, but being equal to the previous one, it will have the same rank of 1. So that's why your solution doesn't work.

If you want it to work, just use row_number instead. This one will only care about the order and not how the row relates to other rows.

    select c.id_number
        ,row_number() OVER (Partition by c.id_number ORDER BY c.id_number) "rank"
    from  children c

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I think you want row_number():

select c.id_number,
       row_number() over (Partition by c.id_number ORDER BY c.id_number) as "rank"
from  children c;

Upvotes: 2

Related Questions