SELECT only rows with either the MAX date or NULL

Question

I need a query that will produce a non duplicate list of all of our members and their corresponding states & countries (along with some other data that is joined from other tables). Each member may have 0-many MemberAddress records. If a member has MemberAddress records, I would like to join only to the record that has been modified most recently. If the member does not have any associated MemberAddress records, I still want the member to show in the list, but the state and country would then be NULL values.

SELECT m.member, ma.state, ma.country FROM Member m
 LEFT OUTER JOIN MemberAddress ma ON m.member = ma.member           
 INNER JOIN (SELECT Member, MAX(Modified) AS MaxDate
        FROM MemberAddress
        GROUP BY Member) AS m2
    ON (ma.Member = m2.Member AND ma.Modified = m2.MaxDate)

This query removes the duplicates caused when a member has multiple MemberAddress records, however it does not allow for members that do not have any MemberAddress records.

How can I alter this query to also show members that do not have any MemberAddress records?

Thanks!!

Edited to add: I'm using SQL 2005

Answer 1

Your version is quite close. You can do it using two left joins:

SELECT m.member, ma.state, ma.country
FROM Member m LEFT OUTER JOIN
     MemberAddress ma
     ON m.member = ma.member LEFT OUTER JOIN
     (SELECT Member, MAX(Modified) AS MaxDate
      FROM MemberAddress
      GROUP BY Member
     ) m2
     ON (ma.Member = m2.Member AND ma.Modified = m2.MaxDate);

Answer 2

You were on the right track, but the join between ma and m2 has to, itself, be an entire subquery. The problem is that your INNER JOIN applies to the whole query, not just to the relationship between ma and m2:

SELECT m.member, mx.state, mx.country
FROM Member m
 LEFT OUTER JOIN (
SELECT ma.state, ma.country, ma.member from MemberAddress ma            
 INNER JOIN (SELECT Member, MAX(Modified) AS MaxDate
    FROM MemberAddress
    GROUP BY Member) AS m2
ON (ma.Member = m2.Member AND ma.Modified = m2.MaxDate)
) mx ON m.member = mx.member

Assuming I didn't typo anything (except that parentheses, which I fixed).

Answer 3

You can try using Row_Number to get the rows with the max date:

SELECT 
     m.member
    ,ma.state
    ,ma.country
FROM Member m
LEFT OUTER JOIN (
    select
         *
        ,row_number() over(partition by Member order by modified desc) as dateOrder
    from MemberAddress
) ma 
    ON m.member = ma.member
    and ma.dateOrder = 1

I'm writing this syntax from memory but I think this will work.

Answer 4

something like below ought to work (untested)

SELECT member, state, country
FROM (
    SELECT m.member, ma.state, ma.country
         , row_number() over (partition by m.member
                          order by ma.Modified desc) as rn 
    FROM Member m
    LEFT JOIN MemberAddress ma 
        ON m.member = ma.member
) as T
WHERE rn = 1

Answer 5

You're very close already. This just needs your INNER JOIN to become LEFT JOIN as you did between Member and MemberAddress. The inner join is causing rows to drop as you have it now.

The answers including the OVER clause are also perfectly legit. There's an MSDN Article for more information on that approach.

Update: adding the query that worked for me:

SELECT DISTINCT m.member, ma.state, ma.country 
  FROM Member m
  LEFT 
  JOIN (SELECT Member, MAX(NVL(Modified, 0)) AS MaxDate, state, country
          FROM MemberAddress
         GROUP 
            BY Member) ma
    ON m.member = ma.member