Eliminating duplicates in Sql when one field is different

morinia
morinia used Ask the Experts™
on
I have a table (Table_A) that has 14 columns.  Column named enrollment_date may change due to annual enrollments.  Multiple enrollment dates is causing duplicate rows.  How can I eliminate the duplicates and keep only the row with the most recent enrollment date?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Developer
Commented:
it should be somthing like this:

delete from tablea A
join tablea B on A.<col> = B.<col> -- other conditions if needed
where A.enrollment_date <= B.Enrollment_date
and A.RowID <> B.RowID


and you can test by first doing:

select * from  (instead of delete from)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial