Help with delete group syntax

[kim]

Here are my 2 colums of my table;

ID LastContact
1a 4/12/2003
2d 5/12/2004
1a 9/12/2006
3c 6/12/2005

...and so on

I need to clean up this table so that I have one unique ID, with the latest 'Last Contact' date....so for this example, after the delete query I would only have '1a' with the '9/12/2006' LastContact colum..and the other two rows would be unaffected, since they didnt have a duplicate id..so after the delete query, my table would look like this;

ID LastContact
2d 5/12/2004
1a 9/12/2006
3c 6/12/2005

How would I write such a query...some sort of grouping ?

[mikr0s]

DELETE mytbl
FROM mytbl a
LEFT JOIN (SELECT ID, MAX(LastContact) AS LastContact FROM mytbl GROUP BY ID) b ON b.ID = a.ID AND b.LastContact = a.LastContact
WHERE b.LastContact IS NULL

--HTH--

[kim]

thanks so much for your answer....that worked..BUT
...it brought up another matter that I did not forsee...

I can have an id with the SAME max LastCont date...same id AND same LastContact date

ID LastContact
2d 5/12/2004
1a 9/12/2006
1a 9/12/2006
3c 6/12/2005
4e 4/9/2005
4e 4/9/2005

I need to have just one row of ID and LastContact...so after the delete query the table should look like this:

ID LastContact
2d 5/12/2004
1a 9/12/2006
3c 6/12/2005
4e 4/9/2005

...it doesnt matter which line I keep....how would my delete query look ?

thank you !

[Michael Raven]

maybe DISTINCT keyword in query helps?