No problem. Hope all is well.
Duey From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Downall Sent: Wednesday, October 08, 2008 10:09 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: SQL Question Sorry, I didn't read closely. I thought the CntID had a 1-1 relationship with the name. On Wed, Oct 8, 2008 at 11:03 AM, Heffelfinger, Duane <[EMAIL PROTECTED]> wrote: 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
