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