Delete duplicate recoed from a table without primary key

Hariinakoti
Posted by Hariinakoti under Sql Server category on | Points: 40 | Views : 1334
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

Comments or Responses

Posted by: Easwaran on: 9/14/2012 Level:Starter | Status: [Member] | Points: 10
But there is no query to delete those duplicate items? Please do provide that as well.
Posted by: Jasminej on: 9/15/2012 Level:Starter | Status: [Member] | Points: 10
Are you trying to DELETE or SELECT the duplicate records ?
Posted by: T.Saravanan on: 9/15/2012 Level:Silver | Status: [Member] [MVP] | Points: 10
Please post your code inside the code tag and update your query with delete script.

Login to post response