Write a sql query to delete duplicates records where there is no uniquely identified column

Question

Anonymous · Accepted Answer

When you don’t have a unique column (like an ID), you can still delete duplicates by using window functions (ROW_NUMBER) in SQL.
Suppose your table is named Employees and has columns:
Name   | Department | Salary
WITH CTE AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Name, Department, Salary   -- columns that define duplicates
            ORDER BY (SELECT NULL)                  -- no unique column
        ) AS rn
    FROM Employees
)
DELETE FROM CTE WHERE rn > 1;

PwC

PwC Interview Question

Interview Answer

Want the inside scoop on your own company?

Bowls

Followed companies

Job searches