How do I delete all but latest record?

[NCYankee1]

I have a SQL table that has a number of fields including these two:
EmpID char(6)
EffDate datetime
There can be 1 or many EmpId's. What I am trying to do is find a simple way to delete all the duplicate EmpID's except the one with the most recent EffDate. For example, if this were my data:
000001 7/5/2005
000002 6/30/2005
000002 7/15/2005
000003 7/10/2005
I would want only these rows remaining after my delete:
000001 7/5/2005
000002 7/15/2005
000003 7/10/2005

Could someone help me with the SQL statement to accomplish this?
Thanks!

 

[vongrunt]

With MAX() check in WHERE clause:

delete A
from myTable A
where EffDate < (select max(EffDate) from myTable B where B.EmpID=A.EmpID)

With derived table and join:

delete A
from myTable A
left outer join
(	select EmpID, max(EffDate) as EffDate
	from myTable
	group by EmpID
) B on B.EmpID=A.EmpID and A.EffDate=B.EffDate
where B.EffDate is null

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[AtomicWedgie]

Does the table also have a primary key?

 

[NCYankee1]

Thanks, vongrunt. Your statement did the trick!

Yes, AtomicWedgie, there is a PK, but it is an autokey so it is out of my way in this case.

Thanks for your help!