Delete duplicate rows not based on primary key

This will keep the oldest AgencyID values, and delete any duplicates otherwise.

;WITH x AS ( SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY VendorID, RegionID, Name, Zip ORDER BY AgencyID) FROM dbo.tblAgencies ) DELETE x WHERE rn > 1; 

Be careful, though; this may not work if other tables reference AgencyID and they've obtained any of your newer, erroneous values.

The simplest solution, use select distinct into a temp table, then reload the original

This query will give you duplicates provided that the combination of all other columns is unique:

select * from mytable t1 where exists (select * from mytable t2 where t1.VendorID = t2.VendorID and t1.RegionID = t2.RegionID and and t1.Name = t2.Name and t1.Zip = t2.Zip and t1.AgencyID > t2.AgencyID) 

This should give you all the rows that have duplicate values except for the minimum agencyid row.

select * from tblAgencies where AgencyID not in (select min(AgencyID) from tblAgencies group by VendorID, RegionID, Name, Zip) 

edit: adding SQLFiddle

;with CTE AS ( SELECT ID_Column, rn = ROW_NUMBER() OVER (PARTITION BY Column1, Column2, Column3... ORDER BY ID ASC) FROM T ) DELETE FROM CTE WHERE rn >= 2 

;with CTE AS (SELECT MAX(AgencyID) AgentID,VendorID , RegionID , Name , Zip FROM tblAgencies GROUP BY VendorID , RegionID , Name , Zip HAVING COUNT(*) > 1) DELETE FROM tblAgencies WHERE EXISTS (SELECT 1 FROM CTE WHERE AgentID = tblAgencies.AgencyID) 

Lots of answers that will give you what you want here, but there's no need to use a CTE or do any grouping, the simplest way is just:

delete t1 from tblAgencies t1 join tblAgencies t2 on t1.VendorId = t2.VendorId and t1.RegionId = t2.RegionId and t1.Name = t2.Name and t1.Zip = t2.Zip and t1.AgencyId > t2.AgencyId 

Maybe this will help: How to delete duplicates in the presence of a primary key?