Hi,

Great Post. This will definately help me. I tried manipulating your code to work for me and i get an "Msg 207, Level 16, State 1, Line 27 Invalid column name 'iID'." when i want o delete the duplicates and retail one copy of the duplicates. The error is with p2.iid. The column is there and i am not sure why i am getting this error.

Can you please help?

--deleteing duplicated rows with keeping 1 copy of the duplicated row.

DELETE FROM

p1

FROM

VehicleHistory p1

INNER JOIN

(

SELECT

MAX(iID) AS ColumnID,

ivehicleid,

dtdatetime,

iOdometer

FROM

VehicleHistory

GROUP BY

ivehicleid,

dtdatetime,

iOdometer

HAVING

COUNT(*) > 1) p2

ON

(p1.ivehicleid = p2.ivehicleid

AND

p1.dtdatetime = p2.dtdatetime

AND

p1.iOdometer = p2.iOdometer

and

p1.iID <> p2.iID)