MS SQL 2008 join - select one from many results

Question

I'm trying to run the following query, but am not sure how to limit it to one result only. In the query below, the client that clientcontactid 21901 works for has 2 address meaning that 2 results return.

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

I need to return one of those results for client contact 21901, with the priority being on the one with the city in it. I've tried select top (1) but I think it's down to the join forcing multiple record back. Any help on how to return only 1 result, and how to control that would be highly appreciated!

Thanks

Answer 1

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

Answer 2

What I normally do to prioritize results, is to actually assign a priority value column. In this case it can be kept simple because there is only one priority: records with cities come before cities without:

with q as(
select  cc.contactpersonid,
    cc.clientcontactid,
    ad.city,
    ad.addressid,
    case when ad.city is null then 0 else 1 end prior
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 top 1 * from q order by prior desc