Extract one row for same key based on a column value oracle

I have a result set in oracle that looks somehow like below with 3 columns: account, client and address:

Account. Client. Address
Abc. Aaa. Mailing
Abc Aaa. Domicile
Ccc. Aaa. Mailing
Ccc. Aaa. Domicile
Ccc. Aaa. Office
Ddd. Bbb. Domicile
Ddd. Bbb. Office

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:

Account. Client. Address
Abc. Aaa. Mailing
Ccc. Aaa. Mailing
Ccc. Aaa. Mailing
Ddd. Bbb. Domicile

>Solution :

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;

Leave a Reply