Eliminate Duplicate Records

Hi

How can i eliminate duplicate records from a single table?

There is a surrogate key in the table and i just can't seem to wrap my head around the layout.

ID | MemberID | Name | Surname | DateOfAssessment | etc.

The ID is unique, but there are duplicate memberID's with different date of assessments, i want to take the last used date of assessment and keep only that one, and remove the rest from the table?

I'm sure the query is simple, i'm just too stupid to see how to solve it!!

Any help would be greatly appreciated.

Kind Regards
Carel Greaves

This should give you an idea of one method to accomplish your goal.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( [ID] int IDENTITY,
MemberID int,
[Name] varchar(20),
SurName varchar(20),
DateOfAssessment datetime
)

INSERT INTO @.MyTable VALUES ( 2, 'Bill', 'Jones', '2007/05/25' )
INSERT INTO @.MyTable VALUES ( 3, 'Mary', 'Smith', '2007/05/26' )
INSERT INTO @.MyTable VALUES ( 4, 'Susy', 'Williams', '2007/05/22' )
INSERT INTO @.MyTable VALUES ( 2, 'Bill', 'Jones', '2007/05/26' )
INSERT INTO @.MyTable VALUES ( 2, 'Bill', 'Jones', '2007/04/26' )

SELECT *
FROM @.MyTable

DELETE @.MyTable
FROM @.MyTable m
JOIN (SELECT
MemberID,
DateOfAssessment = max( DateOfAssessment )
FROM @.MyTable
GROUP BY MemberID ) dt
ON ( m.MemberID = dt.MemberID
AND m.DateOfAssessment <> dt.DateOfAssessment
)

SELECT *
FROM @.MyTable

ID MemberID Name SurName DateOfAssessment
--
2 3 Mary Smith 2007-05-26 00:00:00.000
3 4 Susy Williams 2007-05-22 00:00:00.000
4 2 Bill Jones 2007-05-26 00:00:00.000

|||
Thanks you very much.