How to delete duplicate records but keep one?

To delete duplicate records but keep one in SQL Server, the most common and safe approach is using ROW_NUMBER().

Method: Using ROW_NUMBER()

This assigns a sequence number to duplicate rows and deletes all except the first.

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY Name, Email   -- columns that define duplicates
               ORDER BY Id                -- keeps the lowest Id
           ) AS rn
    FROM Users
)
DELETE FROM CTE
WHERE rn > 1;

How It Works

• PARTITION BY → groups duplicate rows (based on columns)
• ROW_NUMBER() → assigns numbers (1, 2, 3...) within each group
• rn = 1 → keeps one record
• rn > 1 → deletes duplicates

Example

Before:

After:

Important Tips

• Always take a backup before delete
• Replace Name, Email with your duplicate columns
• Change ORDER BY to control which record you keep (e.g., latest date)

Alternative (Keep Latest Record)

ROW_NUMBER() OVER (
    PARTITION BY Name, Email
    ORDER BY CreatedDate DESC
)

Final Thought

ROW_NUMBER() is the most reliable and flexible way to remove duplicates while keeping one clean record.