You can do this by creating an AddressPriority table:
Priority Countrycode
1 'US'
2 'CA'
3 'MX'
and then use a query similar to this:
WITH Prio as (
SELECT CustomerID,Min(priority)
FROM customeraddress ca join AddressPriority ap ON ca.countrycode = p.countrycode
GROUP BY CustomerID
)
SELECT *
FROM customer c JOIN prio p ON p.CustomerID = c.CustomerID
JOIN AddressPriority ap ON p.Priority = ap.Priority
JOIN CustomerAddress ca ON ca.CustomerID = c.CustomerID
AND ca.CountryCode= ap.CountryCode
Using a Select Case instead:
WITH Prio as (
SELECT CustomerID,Min(priority)
FROM (
Select CustomerID
,Case CountryCode
When 'US' Then 1
When 'CA' Then 2
When 'MX' Then 3
END as Priority
FROM CustomerAddress
) as Prio
GROUP BY CustomerID
)
,addressprio as (
select CustomerID
,Case prio
when 1 then 'US'
when 2 then 'CA'
When 3 Then 'MX'
End as CountryCode
From Prio
)
SELECT *
FROM customer c JOIN addressprio ap ON ap.CustomerID = c.CustomerID
JOIN CustomerAddress ca ON ca.CustomerID = c.CustomerID
AND ca.CountryCode = ap.CountryCode