How to simply delete duplicate records in SQL Server?

Question

In my table I have a few duplicate records. I want to simply delete them and leave one entry in the table.

Columns which matching is [SourceGradeId] from table A.

Can anyone show me a very simple delete query rather altering the table?

Answer 1

You can run below query to delete duplicate records based on "SourceGradeId" column form table A. Be careful as this will delete many rows, take backup of your data before executing this

With Duplicates as
(
   select * , row_number() over(partition by SourceGradeId order by SourceGradeId ) rownumber 
   from A  
) delete from Duplicates where rownumber!=1

Answer 2

Use row_number. This assumes there is another column or columns that help identify which to keep.

;with r as (
    select SourceGradeId, 
           row_number() over (partition by SourceGradeId order by <other col>) rn
    from A
 )
 delete r where rn>1