I have a result set in oracle that looks somehow like below with 3 columns: account, client and address:
In case I have a key pair client/account that has multiples address declared I need to get only the mailing one and in case I don’t have a mailing address declared I need to take only the domicile one.
These should be the results:
You can find the first row for each account and use a
CASE expression to set the priority:
SELECT account, client, address FROM ( SELECT account, client, address, ROW_NUMBER() OVER ( PARTITION BY account ORDER BY CASE address WHEN 'mailing' THEN 1 WHEN 'domicile' THEN 2 ELSE 3 END ) AS rn FROM table_name ) WHERE rn = 1;