---
title: "Help with Writing a Query to Remove Duplicates from a Table in SQL Server"  
description: "Help with Writing a Query to Remove Duplicates from a Table in SQL Server"  
author: "Anubhav Sharma"  
published: 2024-07-15  
updated: 2024-07-16  
canonical: https://www.mindstick.com/forum/160918/help-with-writing-a-query-to-remove-duplicates-from-a-table-in-sql-server  
category: "SQL Server"  
tags: ["database", "sql server", "sql server 2008", "sql server 2012", "sql server 2022"]  
reading_time: 3 minutes  

---

# Help with Writing a Query to Remove Duplicates from a Table in SQL Server

Hi all,

I need to write a query to [remove duplicate](https://www.mindstick.com/forum/157765/how-to-remove-duplicate-rows-in-sql) rows from my `Customers` table based on the `Email` column. Can [someone help me](https://answers.mindstick.com/qa/42311/can-someone-help-me-with-this-question-about-political-parties) with this?

## Replies

### Reply by Ravi Vishwakarma

To [remove](https://yourviews.mindstick.com/story/4554/8-harmful-weeds-to-remove-from-garden) duplicates from a table in SQL Server, you typically need to identify the duplicates based on certain columns and then delete the extra rows while retaining one instance of each [duplicate](https://www.mindstick.com/forum/160911/sql-query-to-find-duplicate-records-in-a-table-in-sql-server) group. Here’s a step-by-step approach:

#### Step 1: Identify Duplicates

First, identify the duplicate rows based on the columns that define the duplicates. You can use a `ROW_NUMBER()` window function to assign a unique number to each row within a group of duplicates.

#### Step 2: Delete Duplicates

Use a [**common table expression (CTE)**](https://www.mindstick.com/blog/304466/how-do-i-use-cte-to-simplify-complex-queries-in-sql-server) or a subquery to delete rows where the row number is greater than 1, thereby retaining only one instance of each duplicate group.

#### Example

Assume you have a table named `MyTable` with columns `ID`, `Column1`, and `Column2`, and you want to remove duplicates based on `Column1` and `Column2`.

#### Step 1: Identify Duplicates

```plaintext
WITH CTE AS (
    SELECT
        ID,
        Column1,
        Column2,
        ROW_NUMBER() OVER (PARTITION BY Column1, Column2 ORDER BY ID) AS RowNum
    FROM
        MyTable
)
SELECT * FROM CTE WHERE RowNum > 1;
```

This query assigns a row number to each row within each group of duplicates defined by `Column1` and `Column2`. Rows with `RowNum` greater than 1 are considered duplicates.

## Example

```plaintext
WITH Order_CTE AS (
	SELECT OrderId, OrderName, OrderFrom, ID,
	ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID DESC) AS RowNo
	FROM Orders
)

SELECT * FROM Order_CTE WHERE RowNo <> 1;
```

#### Step 2: Delete Duplicates

```plaintext
WITH CTE AS (
    SELECT
        ID,
        Column1,
        Column2,
        ROW_NUMBER() OVER (PARTITION BY Column1, Column2 ORDER BY ID) AS RowNum
    FROM
        MyTable
)
DELETE FROM CTE WHERE RowNum > 1;
```

## Explanation:

- `WITH CTE AS (...)`: Defines a Common Table Expression (CTE) named `CTE`.
- `ROW_NUMBER() OVER (PARTITION BY Column1, Column2 ORDER BY ID) AS RowNum`: Assigns a unique row number to each row within the partition defined by `Column1` and `Column2`. Rows are ordered by `ID`.
- `DELETE FROM CTE WHERE RowNum > 1`: Deletes rows from the CTE where `RowNum` is greater than 1, effectively removing duplicates and keeping only the first occurrence of each group.

```plaintext
WITH Order_CTE AS (
	SELECT OrderId, OrderName, OrderFrom, ID,
	ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID DESC) AS RowNo
	FROM Orders
)

--SELECT * FROM Order_CTE WHERE RowNo <> 1;
Delete FROM Order_CTE WHERE RowNo <> 1; -- Delete duplicate data
```

#### Important Note:

Ensure that you have a backup of your data before performing delete operations, as this action cannot be undone.

If your table has a primary key or a unique identifier (like `ID` in this example), this approach works well. If not, you may need to adapt the query to suit your specific table schema.

## Read more

[**Write a query to n-th highest salary.**](https://www.mindstick.com/interview/33937/write-a-query-to-n-th-highest-salary)

[**Optimize SQL Server for high-concurrency workloads?**](https://www.mindstick.com/articles/336409/optimize-sql-server-for-high-concurrency-workloads)

[**Designing a normalized database schema in SQL Server**](https://www.mindstick.com/articles/336393/designing-a-normalized-database-schema-in-sql-server)

[**How to use SQL Server indexing to optimize query performance?**](https://www.mindstick.com/articles/336392/how-to-use-sql-server-indexing-to-optimize-query-performance)

[**Explain the Dynamic SQL Query with examples in SQL Server.**](https://www.mindstick.com/articles/336382/explain-the-dynamic-sql-query-with-example-in-sql-server)


---

Original Source: https://www.mindstick.com/forum/160918/help-with-writing-a-query-to-remove-duplicates-from-a-table-in-sql-server

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