Sunday, February 19, 2012

Remove duplicate rows from a table in SQL Server


You can see following is the table with duplicate records:-
  

SELECT * FROM Employee;

 


















Before going to delete the records we need to learn, how to get duplicate records in our table?
Following query will show you how to get Employee ID, which are duplicate:-

SELECT MIN(id),name,address,gender,salary
FROM Employee
GROUP BY name,address,gender,salary;











Following query will be used for getting ID of duplicate records:-

SELECT MIN(id) FROM Employee
GROUP BY name,address,gender,salary;










Select * from Employee
Where id NOT IN
(
 SELECT MIN(id) FROM Employee
 GROUP BY name,address,gender,salary
)














Delete from Employee
Where id NOT IN
(
     SELECT MIN(id) FROM Employee
     GROUP BY name,address,gender,salary
)









Select * from employee;
 

 

 

 

 

 





















0 comments: