SELECT * FROM
(
  SELECT
    a.agreement_number,
    customer,
    a.customer_account_name,
    a.customer_account_number,
    a1.*,
    ROW_NUMBER() OVER (PARTITION BY a.agreement_id ORDER BY a1.address_type ASC) AS rn
  FROM agreements a
  LEFT JOIN customers c ON a.customer = c.customer_id
  LEFT OUTER JOIN ADDRESSES a1 ON a1.address_id IN (c.legal_address,c.forwarding_address,c.bank_account_name_address)
)
WHERE rn =1