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

score 1 · Answer 1 · answered Jun 04 '14 at 20:21

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);

answered Jun 04 '14 at 20:21

Gordon Linoff

1,242,037
58
646
786

Changing INNER to LEFT OUTER is causing duplicates for me – Amy Jun 04 '14 at 20:28
1

Yes, you need to put the entire ma/m2 query in a subselect (I posted a version before I saw this). Two left joins alone doesn't reduce the number of records in ma, which will cause duplicates. – Chipmonkey Jun 04 '14 at 20:35

Chipmonkey · Accepted Answer · 2014-06-04T20:43:18.877

1

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).

edited Jun 04 '14 at 20:43

answered Jun 04 '14 at 20:31

Chipmonkey

863
7
18

I had to move "ON m.member = ma.member" to after ") mx" and change to use "mx", but this worked otherwise. Thank you! – Amy Jun 04 '14 at 20:39
Ack! I should have built dummy tables to test better, sorry for the typo. ;-) – Chipmonkey Jun 04 '14 at 20:42

score 0 · Answer 3 · answered Jun 04 '14 at 20:19

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.

score 0 · Answer 4 · answered Jun 04 '14 at 20:21

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

clarkitect · Answer 5 · 2014-06-04T20:38:20.043

0

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

edited Jun 04 '14 at 20:38

answered Jun 04 '14 at 20:22

clarkitect

1,720
14
23

yes. The second `JOIN` still produces rows that would be dropped by the `INNER` and included by `LEFT` (or, I guess, `FULL` ;) – clarkitect Jun 04 '14 at 20:28
Changing INNER to LEFT OUTER is causing duplicates on the rows that have multiple MemberAddress records. – Amy Jun 04 '14 at 20:29

SELECT only rows with either the MAX date or NULL

5 Answers5

Linked