Thank you Larry and Bill,
I believe Larry's solution was what I was looking for. Now I have to get my mind around the solution to adapt it to a little more complicated need and for future uses. Bill I think your result will produce all rows as the cntid is an auto numbered unique row identifier where I'm trying to group by name even though I want the cntId as the result. Golf plans? Duey From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Lawrence Lustig Sent: Wednesday, October 08, 2008 9:15 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: SQL Question << I want an sql statement that produces the contact id for the most current contact of each individual. The result should be: >> SELECT CntID FROM Contacts WHERE NOT EXISTS (SELECT * FROM Contacts C2 WHERE C2.CntNme = Contacts.CntNme AND C2.CntDte > Contacts.CntDte) should do the trick. If you have two contacts for the same person on the same date, you'll get back BOTH IDs with this system. If that's a problem do: SELECT CntNme, MAX(CntID) FROM Contacts WHERE NOT EXISTS (SELECT * FROM Contacts C2 WHERE C2.CntNme = Contacts.CntNme AND C2.CntDte > Contacts.CntDte) GROUP BY CntNme -- Larry
