Remove duplicate records from a table in SQL Server

Many developers face this problem, that they have column with duplicate data, and want to delete duplicate records from SQL table , but one should never delete duplicate record without observing that ,that what  is going to delete ,and prefer to have latest record. So I am explain some steps to delete duplicate records from table and assuming that and table has unique incremental id.

1.Table  with duplicate record:-

insert into student values('yashodhara')

insert into student values('New')

insert into student values('New')

insert into student values('Old')

insert into student values('Old')

insert into student values('Avarage')

insert into student values('Keep')

insert into student values('Keep')

insert into student values('After')

2. check data

select * from student

Output is:

3.To find which record is duplicate and how many times it is repeated

 Select name,COUNT(*)as duplicate from student group by name having count(*)>1 order by count(*) desc

Out put is 

4. Delate duplicate records and keep lates record of every dulicate data

delete from student where rollno not in(

select max(rollno) from student group by name

)

5 Check final data

select * from student

                                                 Author: Er. Yashodhara Sharma