How to delete Duplicate rows from a table which are having same values for all the columns and without using window functions?

Question

My Table is

+-----+----+----+------+
|EmpID|Name|Dept|Deptno|
+-----+----+----+------+
|1    |Abc |xyz |10    |
|1    |Abc |xyz |10    |
|2    |Def |pqr |20    |
+-----+----+----+------+

I want the output table as

+-----+----+----+------+
|EmpID|Name|Dept|Deptno|
+-----+----+----+------+
|1    |Abc |xyz |10    |
|2    |Def |pqr |20    |
+-----+----+----+------+

Conditions:

Don't Use window functions.
Table doesn't have any keys.
Don't Use Temporary Tables.

I tried a Query:

delete from table_name where EmpId IN (
select EmpID from table_name group by EmpId,Name,Email,Deptno having count(*)>1 )

But, this query deletes all the rows which are having count > 1. But, I trying to delete the duplicate rows except one

Just to make sure. Do you have to delete that duplicates row from your table or do you want your output to have no duplicates? — DenStudent, Oct 25 '16 at 07:07
@DenStudent Yea.. I want to delete the duplicates in the same table.. — Kurella Hemanth, Oct 25 '16 at 12:14
@jarlh Without those conditions, the problem is easy to solve. The real problem lies in conditions. I want to find other way of solving the problem accepting all the conditions. — Kurella Hemanth, Oct 25 '16 at 12:16

score 0 · Answer 1 · answered Oct 25 '16 at 07:07

0

Use CTE + ROW_NUMBER to delete the duplicates alone

;WITH cte
     AS (SELECT Row_number()OVER(partition BY EmpId, Name, Email, Deptno ORDER BY (SELECT NULL)) AS rn,*
         FROM   table_name)
DELETE FROM cte
WHERE  rn > 1

I think you can keep only EmpId in Partition BY

answered Oct 25 '16 at 07:07

Pரதீப்

91,748
19
131
172

I know your answer before... But, I am trying to find other way without window functions... – Kurella Hemanth Oct 25 '16 at 09:28
@KurellaHemanth - DO you have any other column which has different value – Pரதீப் Oct 25 '16 at 09:38

score 0 · Answer 2 · answered Oct 25 '16 at 07:11

0

WITH CTE AS 
(
   SELECT EmpID,Name,Dept,Deptno,
       RN = ROW_NUMBER()OVER(PARTITION BY EmpID,Name,Dept,Deptno ORDER BY EmpID,Name,Dept,Deptno)
   FROM MyTable     
)
DELETE FROM CTE WHERE RN > 1

answered Oct 25 '16 at 07:11

Zeina

1,573
2
24
34

score -1 · Answer 3 · answered Oct 25 '16 at 07:20

-1

Apply Below 3 Query For Your Problem.

1.) ALTER TABLE dbo.test ADD AUTOID INT IDENTITY(1,1)

2.) SELECT * FROM dbo.test WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _ FROM dbo.test GROUP BY EmpId,Name,dept,Deptno)

3.) DELETE FROM dbo.test WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _ FROM dbo.test GROUP BY EmpId,Name,dept,Deptno)

answered Oct 25 '16 at 07:20

krunal modi

135
10

This is same like using Row_Number()... Any other way please?? – Kurella Hemanth Oct 25 '16 at 09:54

How to delete Duplicate rows from a table which are having same values for all the columns and without using window functions?

3 Answers3