Removing oldest record from table - need SQL

[newcoder54]

This is a miniature print of my table. actually many more rows and fields. What is the SQL to remove (DELETE I suppose)the oldest row for a given NUM? If there is only 1 record for the NUM field than the row shold be kept. This is a simpler view of a earlier post today (and corrected).

thanks - Larry

NUM DATE/TIME FIELD 3 ETC
8554-12412 1/3/2002 8:12:16 AM
8554-12412 1/3/2002 8:15:16 AM
8652-12417 1/5/2002 7:51:22 AM
8652-12417 1/3/2002 6:52:19 AM

 

[gol4]

DELETE *
FROM mytable
WHERE (((mytable.dtdate)=(select Max([dtdate]) as maxdt from mytable as a)));

 

[newcoder54]

Hi and thanks much!!

What if I have some rows exactly alike. How do I get rid of all but one?

Larry

 

[cmmrfrds]

This is untested so make the delete a select first with A.NUM and A.yourdate. If it looks okay then go ahead with the delete.

Delete From table A
Where A.yourdate =
(Select max(B.yourdate) From table B
Where A.NUM = B.NUM Group by B.NUM
Having count(B.NUM) > 1)