Delete duplicate rows on table with/without primary key in Sql Server 2005

 Posted by Sksingh on 6/7/2011 | Category: Sql Server Interview questions | Views: 6561 | Points: 40
Answer:

Here below query that have multiple repeated records, my aim to delete duplicate records and keep one record for each unique row.

select * from employee


insert into employee(empid,empname,salary)
select 1,'A',20000 union all
select 1,'A',20000 union all
select 1,'A',20000 union all
select 1,'A',20000 union all
select 2,'B',20000 union all
select 1,'C',20000 union all
select 2,'B',40000

-- delete duplicate records based on name
set rowcount 1
delete from employee where ( select count(*) from employee ee where employee.Empname = ee.Empname) > 1

while (@@rowcount > 0)
begin
delete from employee where ( select count(*) from employee ee where employee.Empname = ee.Empname) > 1
end

set rowcount 0


Output :
1 A 20000
1 C 20000
2 B 40000


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Jayeshl on: 6/9/2011 | Points: 10
thanks dear to post this solution..


Login to post response