Showing posts with label how to delete duplicate records in sql query. Show all posts
Showing posts with label how to delete duplicate records in sql query. Show all posts

Wednesday, February 4, 2015

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