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:

CodePudding user response：

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;