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

Question

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

Answer 1

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;