>>Re: Always trips me up! Thread #1233129
>
>No, NO WAY I would read this AGAIN :-)

To summarize ideas discussed in that thread.

Assuming you have 2 tables Parent & Child and you want to find records with maximum date in the Child table, here are different ways to do it:

SELECT * FROM Members mb
  JOIN StatusHistory sh ON sh.MemberId = mb.MemberId
  WHERE sh.ChangeDate = (SELECT MAX(ChangeDate) FROM StatusHistory WHERE MemberId = sh.MemberId)

Works in all VFP versions, would return multiple records if there are more than one record in the child table with the same maximum date.

SELECT Mb.*, Sh.* ;  
  FROM Members mb ;  
  LEFT JOIN StatusHistory sh ON Mb.ID=sh.ID ;  
  WHERE Sh.StatusID IS NULL ;  
  	OR sh.StatusID IN ;  
  		(SELECT CTOBIN(RIGHT(MAX(DTOS(ChangeDate)+BINTOC(StatusID)),4)) ;  
  			FROM StatusHistory GROUP BY ID)

Idea by Igor Korolev from http://forum.foxclub.ru/read.php?32,177183,177232#msg-177232 (in Russian)
(the code assumes that ID field is an integer, it would be simpler for character PKs).

Would return one record always.
Third way:

SELECT * ;  
  FROM Members mb ;  
  INNER JOIN; 
    (SELECT ff.* FROM StatusHistory ff;
         INNER JOIN (SELECT MemberId, MAX(ChangeDate) AS ChangeDate;
                            FROM StatusHistory ttt;
                            GROUP BY MemberId) ss;
               ON ff.MemberId = ss.MemberId AND;
                  ff.ChangeDate = ss.ChangeDate) sh;
   ON Mb.MemberId =sh.MemberId

Works in VFP9 only and would also return multiple records.

This may be a nice FAQ or a page on a website, since the problem seems to be common enough.

In SQL Server the second query can also work with the slight modification.