Munavvar Munavvar - 7 days ago 7
SQL Question

Delete duplicate records without using ROW_NUMBER() function

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

Answer

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)