Delete duplicate rows with no primary key

See this example
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

Thanks & Regards
Hari

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

If u satisfy with my answer please click on Mark As Answer

Thanks & Regards
Hari

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

We can do it two ways.
1. Using SET ROWCOUNT <Required No> ON
2. Using the TOP keyword as below.

For example:
One way

SELECT * FROM dbo.duplicateTable 



SET ROWCOUNT 1 

DELETE FROM dbo.duplicateTable WHERE ID = 1 

SET ROWCOUNT 0 



SELECT * FROM dbo.duplicateTable

Alternate Way

SELECT * FROM dbo.duplicateTable

DELETE TOP(1) FROM dbo.duplicateTable WHERE ID = 1 

SELECT * FROM dbo.duplicateTable

Hope this explains you better.

Please let me know if you need further explanation.

Thanks and Regards,
Gayathri P

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Create a temporary table which stores the distinct records of the original table and then rename it...... 

Thanks and Regards
V.SaratChand
Show difficulties that how difficult you are

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Thank you to all..

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Please Mark As Answer if u satisfy with my answer.

Thanks & Regards
Hari

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

@Pgayath Good Explanation..

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

@Harii,I want delete query..but it is select query

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

@Pgayath,
If there are huge records,we cant apply top 1 for deleteing duplicate data

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Hi Muhsinathk,
here is a simple query.. SQL Server maintains the physical location of the row rather rowid as in oracle.

Query:

delete from DuplicateRowsDlt where (%%physloc%%) not in (select MIN(%%physloc%%) from DuplicateRowsDlt group by FirstName)

Explanation:
subquery: First group by the column based on which you want to delete the rows. Min() gets the firstrow of the group.
Outerquery: deletes all the rows except the min row (specified with not in).

Regards.

Awesome Coding !! :)

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Thank you Vasanth

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

If the issue is resolved, plz mark it as answer. :)

Awesome Coding !! :)

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Good work Vasanth

Thanks & Regards
Hari

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

hi
Muhsinathk
try this using Row_number put your delete query

SELECT ROW_NUMBER() OVER(ORDER BY DepartmentID DESC) AS Row,LastName 

from dbo.employee 

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

DELETE TOP(1) FROM dbo.duplicateTable WHERE ID = 1


DELETE * FROM EMPLOYEE WHERE EMPID NOT IN (SELECT EMPID FROM EMPLOYEE GROUP BY EMPID HAVING COUNT(*)<1)



HOPE THIS WILL HELP................

sriram

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator