---
title: "How to delete duplicate records but keep one?"  
description: "How to delete duplicate records but keep one?"  
author: "Ravi Vishwakarma"  
published: 2026-03-17  
updated: 2026-04-29  
canonical: https://answers.mindstick.com/qa/116428/how-to-delete-duplicate-records-but-keep-one  
category: "database"  
tags: ["sql server", "database"]  
reading_time: 2 minutes  

---

# How to delete duplicate records but keep one?

## Answers

### Answer by Anubhav Sharma

To **delete [duplicate records](https://answers.mindstick.com/qa/116620/how-do-you-find-duplicate-records-in-a-sql-server-table) but keep one** in [SQL Server](https://www.mindstick.com/articles/807/remove-delete-duplicate-records-or-rows-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.

```plaintext
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:

| Id | Name | Email |
| --- | --- | --- |
| 1 | A | a@test.com |
| 2 | A | a@test.com |
| 3 | B | b@test.com |

After:

| Id | Name | Email |
| --- | --- | --- |
| 1 | A | a@test.com |
| 3 | B | b@test.com |

## 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)

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

## Final Thought

`ROW_NUMBER()` is the **most [reliable](https://www.mindstick.com/blog/11933/how-to-play-with-a-smartphone-in-a-reliable-and-honest-casino) and flexible way** to [remove duplicates](https://www.mindstick.com/forum/160918/help-with-writing-a-query-to-remove-duplicates-from-a-table-in-sql-server) while [keeping one](https://answers.mindstick.com/qa/116622/how-do-you-delete-duplicate-rows-while-keeping-one-record-in-sql-server) clean record.


---

Original Source: https://answers.mindstick.com/qa/116428/how-to-delete-duplicate-records-but-keep-one

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.