SQL - Removing Duplicate Rows but Keeping Null Rows

Question

Happy New Year SO!

Problem

I'm writing a stored procedure that takes in a list of User Ids and it should return 1 record per user id that contains:

1. User details from the User table (first name, last name etc)
2. Latest modified address record from the Address table. If there IS NO Address record, then we need to return NULL in the address fields (Address, Postcode etc)
3. Country details from the Country table (Region etc)

Now I have the following, which is correctly returning NULL's for no address details (last record in the screen shot) BUT for a User Id that has multiple address records, I'm having multiple records returned and NOT the lastest modified address record:

SELECT  
    U.Id, U.FirstName, U.Surname, U.Email, U.DateOfBirth,
    AD.AddressLine1, AD.AddressLine2, AD.AddressLine3,
    AD.PostCode, AD.Nickname, AD.Phone, AD.Modified,
    CNT.Name, CNT.Code,
    a.MaxDate
FROM 
    @TableVariable AS List
LEFT JOIN 
    dbo.Users AS U ON List.Id = U.Id
LEFT JOIN 
    dbo.Addresses AS AD ON U.Id = AD.User_Id
LEFT JOIN  
    (SELECT  
         JA.User_Id, MAX(CONVERT(DATE,JA.Modified,10)) AS MaxDate
     FROM dbo.Addresses AS JA
     GROUP BY JA.User_Id) A ON (AD.User_Id = A.User_Id AND CONVERT(DATE,AD.Modified,10) = A.MaxDate)
LEFT JOIN 
    dbo.Countries AS CNT ON AD.Country_Id = CNT.Id
ORDER BY 
    AD.Modified DESC

Here is the result set after running the above. As you can see I have correctly got my record returned for a User WITHOUT an address (Last record) but I'm getting 3 records for 2108 when I wanted 1, inlcuding the latest modified address (AD.Modified).

I'm using SQL Server 2008.

Answer 1

You can use outer apply and order by to get the latest record, with something like this:

FROM @TableVariable AS List
    LEFT JOIN dbo.Users AS U
        ON List.Id = U.Id
    OUTER APPLY  (
        SELECT top 1 *
        FROM dbo.Addresses AS JA
        WHERE U.Id = JA.User_Id
        order by Modified DESC
    ) AD
    LEFT  JOIN dbo.Countries AS CNT
        ON AD.Country_Id = CNT.Id
    ORDER BY AD.Modified DESC