MS SQL 2008 join - select one from many results

join, sql, sql-server-2008

Solution

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

Problem

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

Original source