Delete duplicate recoed from a table without primary key

Posted by Hariinakoti under Sql Server category on 9/13/2012 | Points: 40 | Views : 1824

Post Code |

Search Codes |

Code Home

create table MaxNthSalary1
( EmpId int not null, EmpName varchar(20) null,Salary money null, CreateDate datetime null,)

insert into MaxNthSalary1 values(1,'pramod',10,getdate())
insert into MaxNthSalary1 values(2,'rakesh',120,getdate())
insert into MaxNthSalary1 values(3,'Jindal',130,getdate())
insert into MaxNthSalary1 values(4,'Manoj',150,getdate())

insert into MaxNthSalary1 values(5,'sanjay',120,getdate())
insert into MaxNthSalary1 values(6,'ravi',150,getdate())
insert into MaxNthSalary1 values(7,'mayank',170,getdate())
insert into MaxNthSalary1 values(8,'pratap',170,getdate())

-- Find duplicate records
insert into MaxNthSalary1 values(5,'sanjay',125,getdate())
insert into MaxNthSalary1 values(6,'ravi',155,getdate())
insert into MaxNthSalary1 values(7,'mayank',175,getdate())
insert into MaxNthSalary1 values(8,'pratap',175,getdate())

select * from MaxNthSalary1 order By EmpName
--Check for dup data.
SELECT EmpId,EmpName FROM MaxNthSalary1
GROUP BY EmpId,EmpName
HAVING COUNT(*) > 1

Alert Moderator