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