How Can I Delete Duplicate Rows in T-SQL While Keeping One Instance?

T-SQL: Clear redundant rows while retaining single row records

Maintaining data integrity is critical, and eliminating duplicate rows is a critical step. In this case, duplicate records occurred due to a data entry error. Your goal is to eliminate these redundant rows, but you need to be precise - making sure you retain a single duplicate.

Solution: Use CTE and ROW_NUMBER()

In SQL 2005 or later, you can use a common table expression (CTE) with the ROW_NUMBER() OVER clause to achieve your goals. The CTE acts as a filter, isolating relevant data, while the ROW_NUMBER() function assigns a sequence number to each row in a predefined partition group (in this case, the key column).

This technique allows you to isolate duplicate rows based on your desired criteria. By appending a WHERE clause that contains a condition that checks whether the row number is greater than 1, you can pinpoint and delete the redundant rows.

Achievement

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

Customization options

The ORDER BY clause in the CTE provides flexibility in sorting rows. If your goal is to keep the newest rows, order the results in descending order by the relevant datetime column (for example, ORDER BY [baz] DESC). Keep in mind that the selection of sorting criteria is completely customizable to your specific needs.

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