How can I remove duplicate rows in SQL Server?

Question

Aryan Kumar · Answer

There are a few ways to remove duplicate rows in SQL Server. Here are some of the most common methods:

Using the DISTINCT keyword

The DISTINCT keyword can be used to select only the distinct rows from a table. For example, the following code will select all of the distinct rows from the Customers table:

SQL

SELECT DISTINCT *
FROM Customers;

This code will remove all of the duplicate rows from the Customers table.

Using the ROW_NUMBER() function

The ROW_NUMBER() function can be used to assign a unique number to each row in a table. For example, the following code will assign a unique number to each row in the Customers table:

SQL

SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNumber, *
FROM Customers;

This code will create a new column called RowNumber that contains a unique number for each row in the Customers table. You can then use the RowNumber column to filter out the duplicate rows. For example, the following code will delete all of the duplicate rows from the Customers table:

SQL

DELETE FROM Customers
WHERE RowNumber > 1;

This code will delete all of the rows in the Customers table where the RowNumber column is greater than 1. This will effectively remove all of the duplicate rows from the table.

Using the GROUP BY clause

The GROUP BY clause can be used to group rows together based on a common value. For example, the following code will group the rows in the Customers table together by the CustomerID column:

SQL

SELECT CustomerID, COUNT(*) AS NumCustomers
FROM Customers
GROUP BY CustomerID;

This code will create a new column called NumCustomers that contains the number of rows in each group. You can then use the NumCustomers column to filter out the duplicate rows. For example, the following code will delete all of the duplicate rows from the Customers table:

SQL

DELETE FROM Customers
WHERE NumCustomers > 1;

This code will delete all of the rows in the Customers table where the NumCustomers column is greater than 1. This will effectively remove all of the duplicate rows from the table.

forum

How can I remove duplicate rows in SQL Server?

Can you answer this question?

1 Answers