---
title: "How do you delete duplicate rows while keeping one record in SQL Server?"  
description: "How do you delete duplicate rows while keeping one record in SQL Server?"  
author: "Anubhav Sharma"  
published: 2026-05-14  
updated: 2026-06-08  
canonical: https://answers.mindstick.com/qa/116622/how-do-you-delete-duplicate-rows-while-keeping-one-record-in-sql-server  
category: "database"  
tags: ["database", "sql server"]  
reading_time: 2 minutes  

---

# How do you delete duplicate rows while keeping one record in SQL Server?

## Answers

### Answer by Ravi Vishwakarma

A common [approach](https://www.mindstick.com/interview/985/what-are-the-approaches-that-you-will-follow-for-making-a-program-very-efficient) in [SQL Server](https://www.mindstick.com/articles/12343/sql-server-2017-ctp-2-0-now-available) is to use a **CTE with** `ROW_NUMBER()` to [identify](https://yourviews.mindstick.com/view/84167/tips-for-students-on-how-to-identify-fake-news) duplicates and delete all but one row.

### Example: Keep One Record, Delete Duplicates

Suppose you have this table:

```plaintext
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);
```

If [duplicate rows](https://www.mindstick.com/forum/157765/how-to-remove-duplicate-rows-in-sql) are defined by the same `Email`, you can do:

```plaintext
-- Identify duplicates
WITH DuplicateRows AS
(
    SELECT
        EmployeeID,
        ROW_NUMBER() OVER (
            PARTITION BY Email
            ORDER BY EmployeeID
        ) AS rn
    FROM Employees
)

-- Delete duplicates, keep the first row
DELETE FROM DuplicateRows
WHERE rn > 1;
```

### How it works

- `PARTITION BY Email` groups rows with the same email.
- `ROW_NUMBER()` assigns:

   - `1` to the row you want to keep.
   - `2`, `3`, etc. to duplicate rows.

- `DELETE WHERE rn > 1` removes all duplicates while retaining one record.

### Keep the Latest Record Instead

If you want to keep the most recent row based on a date [column](https://www.mindstick.com/forum/2288/column-site_name-ambiguous-problem):

```plaintext
WITH DuplicateRows AS
(
    SELECT
        EmployeeID,
        ROW_NUMBER() OVER (
            PARTITION BY Email
            ORDER BY CreatedDate DESC
        ) AS rn
    FROM Employees
)
DELETE FROM DuplicateRows
WHERE rn > 1;
```

This keeps the newest record for each email.

### Preview Before Deleting

It's a good [practice](https://www.mindstick.com/forum/1974/wcf-interview-questions-and-answers-to-practice) to inspect the rows first:

```plaintext
WITH DuplicateRows AS
(
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Email
            ORDER BY EmployeeID
        ) AS rn
    FROM Employees
)
SELECT *
FROM DuplicateRows
WHERE rn > 1;
```

This shows exactly which rows would be deleted before you run the `DELETE` statement.

### Alternative: Remove Completely Identical Rows

If duplicates are based on all columns except the [primary key](https://www.mindstick.com/blog/474/remove-primary-key-from-access-table):

```plaintext
WITH DuplicateRows AS
(
    SELECT
        EmployeeID,
        ROW_NUMBER() OVER (
            PARTITION BY Name, Email
            ORDER BY EmployeeID
        ) AS rn
    FROM Employees
)
DELETE FROM DuplicateRows
WHERE rn > 1;
```

This keeps one copy of each `(Name, Email)` combination and deletes the rest.


---

Original Source: https://answers.mindstick.com/qa/116622/how-do-you-delete-duplicate-rows-while-keeping-one-record-in-sql-server

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