How Can I Delete Duplicate Rows in SQL Server While Keeping One Row?

Efficiently Removing Duplicate Rows in SQL Server While Preserving a Single Row

Data integrity is critical in database management. This article addresses the common issue of removing duplicate rows from a SQL Server table while ensuring at least one instance of each unique data set remains. T-SQL provides a straightforward solution using common table expressions (CTEs).

The OVER clause within the CTE is key to this process. Here's an illustrative example:

<code class="language-sql">WITH cte AS (
  SELECT [foo], [bar],
     ROW_NUMBER() OVER (PARTITION BY [foo], [bar] ORDER BY [baz]) AS rn
  FROM [TABLE]
)
DELETE FROM cte
WHERE rn > 1;</code>

This query uses a CTE to assign a unique row number (rn) to each row within groups defined by the columns [foo] and [bar]. The ORDER BY [baz] clause determines which row within each group is kept; you should adjust this based on your specific needs. The DELETE statement then removes all rows where rn is greater than 1, leaving only one representative row for each unique combination of [foo] and [bar].

This approach offers an efficient and reliable method for maintaining data cleanliness and accuracy in your SQL Server database.

The above is the detailed content of How Can I Delete Duplicate Rows in SQL Server While Keeping One Row?. For more information, please follow other related articles on the PHP Chinese website!