WITH PreferredAddresses as 
	(
		SELECT c.CustomerID, AddressID
		FROM Customers c OUTER APPLY
		  (SELECT TOP 1 *
			FROM CustomerAddresses a
			WHERE a.CustomerID = c.CustomerID
			ORDER BY AddressTypeID, AddressID
			) a
	)
	SELECT c.*, a.AddressID, a.AddressTypeID, OrganizationName, 
	AddressLine1, AddressLine2, AddressLine3, City, a.StateProvinceID, 
	ZipCode, Honorarium, StateProvinceAbbrev, TypeName
	FROM  

WITH PrimaryAddresses as 
(

SELECT 
DISTINCT c.CustomerID
, (SELECT TOP 1 AddressID 
	FROM CustomerAddresses ca 
	WHERE ca.CustomerID=c.CustomerID
	ORDER BY AddressTypeID ASC, AddressID DESC) as AddressID 
	FROM Customers c )
SELECT pa.CustomerID
, pa.AddressID
, ca.AddressTypeID
, at.TypeName
, OrganizationName
, AddressLine1
, AddressLine2
, AddressLine3
, City
, ca.StateProvinceID
, s.StateProvinceAbbrev
, ZipCode
FROM PrimaryAddresses pa 
	LEFT JOIN CustomerAddresses ca ON pa.AddressID=ca.AddressID
	LEFT JOIN AddressTypes at on ca.AddressTypeID=at.AddressTypeID
	LEFT JOIN StateProvinces s on ca.StateProvinceID=s.StateProvinceID
WHERE pa.AddressID IS NOT NULL