Q19: How do you identify and remove duplicate rows from a table?
To remove duplicates while preserving one original record, use a CTE containing ROW_NUMBER() partitioned by duplicate key attributes.
Safely Removing Duplicates with ROW_NUMBER()
WITH DuplicateCTE AS (
SELECT id, email,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY id ASC
) AS row_num
FROM users
)
DELETE FROM users
WHERE id IN (
SELECT id FROM DuplicateCTE WHERE row_num > 1
);💡 ROW_NUMBER assigns row_num = 1 to the first instance of each email. Rows with row_num > 1 are duplicates and get removed.
Key Takeaways
- Partitioning by duplicate key attributes identifies redundant records reliably.
- Always filter using primary keys or row IDs to prevent deleting entire matching record groups.