Delete duplicate records without using ROW_NUMBER() function

Question

I want to delete duplicate records without using ROW_NUMBER() function (SQL Server)

Example: Table with the following data:

name     salary
-----------------
Husain   20000.00
Husain   20000.00
Husain   20000.00
Munavvar 50000.00
Munavvar 50000.00

After deleting the duplicate records table should contains data like this:

name     salary
-----------------
Husain   20000.00
Munavvar 50000.00

No only two column (name,salary). interviewer asked me this question in my recent interview. — Munavvar, Jun 01 '16 at 06:23

score 9 · Accepted Answer · edited Jan 18 '21 at 12:31

9

As the motivation for this question seems to be academic interest rather than practical use...

The table has no primary key but the undocumented pseudo column %%physloc%% can provide a substitute.

DELETE T1
FROM YourTable T1 WITH(TABLOCKX)
WHERE CAST(T1.%%physloc%% AS BIGINT)
NOT IN (SELECT MAX(CAST(%%physloc%% AS BIGINT))
        FROM YourTable 
        GROUP BY Name, Salary)

In reality you should never use the above and just use row_number as it is more efficient and documented.

(Data Explorer Demo)

edited Jan 18 '21 at 12:31

Community

1
1

answered Jun 01 '16 at 06:30

Martin Smith

438,706
87
741
845

Thank you Martin for your answer. – Munavvar Jun 01 '16 at 06:40

score 4 · Answer 2 · answered Jun 01 '16 at 06:36

Another (academic) option, depending on what version of SQL server you're using:

;with CTE as (select lag(name) over (order by name) as name1
              ,lag(salary) over (order by name) as salary1
              , * 
              from #table)

delete from cte where name = name1 and salary = salary1

sagi · Answer 3 · 2016-06-01T06:50:40.900

3

You can use Common Table Expression combined with ROW_NUMBER() like this (This is the best way to delete duplicates):

WITH CTE AS(
   SELECT t.name,t.salary
          ROW_NUMBER() OVER(PARTITION BY t.name,t.salary ORDER BY (SELECT 1)) as rn
   FROM YourTable t
)
DELETE FROM CTE WHERE RN > 1

ROW_NUMBER() will assign each group randomly ranking, only one will get the rank 1 , and every thing else will be deleted.

EDIT: I can suggest something else with out the use of ROW_NUMBER() :

SELECT distinct t.name,t.salart
INTO TEMP_FOR_UPDATE
FROM YourTable t;

TRUNCATE TABLE YourTable ;

INSERT INTO YourTable 
SELECT * FROM TEMP_FOR_UPDATE;

DROP TEMP_FOR_UPDATE;

This will basically create a temp table containing distincted values from your table, truncate your table and re insert the distincted values into your table.

edited Jun 01 '16 at 06:50

answered Jun 01 '16 at 06:08

sagi

40,026
6
59
84

3

Which part of **without** did you not understand? – Raj Jun 01 '16 at 06:10
Is CTE something special? – miltonb Jun 01 '16 at 06:10
@Raj Just because the OP says not to use `ROW_NUMBER()` does not make it a bad solution. – Tim Biegeleisen Jun 01 '16 at 06:10
Huh, I missed that part, just woke up :( @Raj – sagi Jun 01 '16 at 06:11
Agreed. But when the OP is specific about not using that function, then providing a solution using that function is kinda backwards, isn't it? – Raj Jun 01 '16 at 06:11
1

@sagi I think you need to delete this answer. – Zein Makki Jun 01 '16 at 06:15
@sagi : Thanks for quick response but I want to delete it without using ROW_NUMBER() function (interview question) – Munavvar Jun 01 '16 at 06:15
Is this the only two columns available? @Munavvar – sagi Jun 01 '16 at 06:16
Deleting using ROW_NUMBER() is already answered. When OP explicitly says without, you should follow the point – FLICKER Jun 01 '16 at 06:17
1

@Munavvar I've also added a solution without `ROW_NUMBER()` – sagi Jun 01 '16 at 06:20
1

@sagi, I would suggest to use `TRUNCATE` instead. – Felix Pamittan Jun 01 '16 at 06:21
@sagi nice.. is there other solution? – Munavvar Jun 01 '16 at 06:28
@Munavvar Not that I can think of, maybe using a variable with `CTE` , but I don't know SQL-Server well enough to tell you how. – sagi Jun 01 '16 at 06:29
1

Your update isn't valid SQL Server syntax. There is no `create table ... As query` in SQL Server. The equivalent is `select ... Into`. Also SQL Server doesn't allow `ORDER BY 1` in rownumber, but does allow `ORDER BY (SELECT 1)`. – Martin Smith Jun 01 '16 at 06:38
@MartinSmith Good to know, fixed. – sagi Jun 01 '16 at 06:50
@FuzzyTree Well, I'm not required to use `#` aren't I? As you see I drop the table afterwords. And the logic came here first, so, after syntax adjustments it still won't count as a copy of a later answer.. – sagi Jun 01 '16 at 07:12
I talked about the logic, didn't know about that difference in SQL-server . – sagi Jun 01 '16 at 07:13

score 1 · Answer 4 · answered Jun 01 '16 at 06:25

1

Select data from your table using group by name , salary (or distinct).
Insert into temp table.
Delete data in original
Copy data from temp table to your original table

answered Jun 01 '16 at 06:25

dcieslak

2,697
1
12
19

score 0 · Answer 5 · answered Jun 01 '16 at 06:15

0

In oracle you can use as below

Delete from table where rowid not in (select max(rowid) from test group by name, salary);

answered Jun 01 '16 at 06:15

Temruzinn

30
3

1

The question is about SQL Server. – Zein Makki Jun 01 '16 at 06:16
otherwise you follow as below Create temporary table emp_temp As Select Distinct name, salary From emp_temp; Truncate table emp; Insert into emp Select * From emp_temp; – Temruzinn Jun 01 '16 at 06:37
people are so eager to give negative flag.. i agree the answer is irrelevant but @Temruzinn already said its for oracle...I am getting rid of at least one negative – danD Nov 07 '19 at 20:46

Delete duplicate records without using ROW_NUMBER() function

5 Answers5