Search code examples
sqloraclerankingrow-number

Which Oracle Analytic Function should I use to get this result?

Probably a dummy question, but it is Sunday and my mind does not work as it should be. I am trying to use an analytic function to get the following

I have this query ( it is just a small example of one record but enough for what I am trying to explain )

   select h.party_number,organization_name,address_line1,address_type 
      ,row_number() over(partition by party_number,organization_name,address_line1 order by address_line1,address_type ) as newrn
from h 
where party_number = 'FI-110584'
order by address_type,party_number;

Which gives me this result

enter image description here

What I really want is to have the same ranking ( so the same number ) for the combination of party_number, organization_name and adress_line1

So, the last column should look like

1

1

2

Because the same party_number, organization_name and address_line should be given the same ranking value.

What analytic function should I use here ? I tried dense_rank and rank with no success.

Thank you all

Solution

  • Your partitioning and ordering is a little off:

    select h.party_number,organization_name,address_line1,address_type 
      ,dense_rank() over(partition by party_number,organization_name order by address_line1 ) as newrn
from h 
where party_number = 'FI-110584'
order by address_type,party_number;
    PARTY_NUMBER ORGANIZATION_NAME ADDRESS_LINE1 ADDRESS_TYPE NEWRN
    FI-110584 NEKAPAINO OY GOLFKENTÄNTIE 4 Default 1
    FI-110584 NEKAPAINO OY GOLFKENTÄNTIE 4 DivAdd 1
    FI-110584 NEKAPAINO OY PL 100 InvAdd 2

    db<>fiddle