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

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

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

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

CodePudding user response：

To me, it looks like it is DENSE_RANK without ADDR column you're looking for:

SQL> with test (party, org, addr) as
  2    (select 'fi-11', 'neka', 'golfe 4' from dual union all
  3     select 'fi-11', 'neka', 'golfe 4' from dual union all
  4     select 'fi-11', 'neka', 'pl 100'  from dual
  5    )
  6  select t.*,
  7    dense_rank() over (partition by party, org order by addr) rnk
  8  from test t
  9  order by addr;

PARTY ORG  ADDR           RNK
----- ---- ------- ----------
fi-11 neka golfe 4          1
fi-11 neka golfe 4          1
fi-11 neka pl 100           2

SQL>

CodePudding user response：

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

CodePudding user response：

You need a DENSE_RANK not ROW_NUMBER -

SELECT h.party_number,
       organization_name,
       address_line1,
       address_type,
       DENSE_RANK() OVER(PARTITION BY party_number,organization_name,address_line1 ORDER BY address_type) as newrn
  FROM h 
 WHERE party_number = 'FI-110584'
 ORDER BY address_type, party_number;