MS SQL 2008 连接 - 从多个结果中选择一个

我正在尝试运行以下查询，但不确定如何将其限制为仅一个结果。 在下面的查询中，clientcontactid 21901 工作的客户端有 2 个地址，这意味着返回 2 个结果。

Query:

select  cc.contactpersonid,
    cc.clientcontactid,
    ad.city,
    ad.addressid
from SavedList sl
inner join ClientContacts cc on cc.ContactPersonId = sl.ObjectId
inner join Clients c on c.ClientID = cc.ClientId
inner join Address ad on c.ClientID = ad.ObjectId
where sl.SavedListId = 2117

Results:

contactpersonid clientcontactid city    addressid
87934           21901                   145186
87934           21901           London  1130705
89778           17275           Leeds   145368

我需要返回这些结果之一以供客户联系21901，优先考虑其中包含城市的那个。我尝试过选择top (1)但我认为这取决于连接迫使多个记录返回。任何有关如何仅返回 1 个结果以及如何控制该结果的帮助将不胜感激！

Thanks

Try:

;WITH a AS (
select  cc.contactpersonid,
    cc.clientcontactid,
    ad.city,
    ad.addressid,
    ROW_NUMBER() OVER (PARTITION BY cc.clientcontactid ORDER BY ad.city DESC) AS RowNum
    from SavedList sl
    inner join ClientContacts cc on cc.ContactPersonId = sl.ObjectId
    inner join Clients c on c.ClientID = cc.ClientId
    inner join Address ad on c.ClientID = ad.ObjectId
    where sl.SavedListId = 2117
)

SELECT  *
FROM    a
WHERE   RowNum = 1