Remove duplicate rows from the table

CREATE TABLE dbo.DistrictSales

(

SalesID INT NOT NULL IDENTITY(1, 1),

SalesGroup NVARCHAR(30) NOT NULL,

DistrictName NVARCHAR(30) NOT NULL,

SalesAmount INT NOT NULL

);

GO

INSERT INTO dbo.DistrictSales

(SalesGroup, DistrictName, SalesAmount)

VALUES

('Telangana', 'Karimnagar', 22000),

('Telangana', 'Hyderabad', 32000),

('Telangana', 'Karimnagar', 22000),

('Telangana', 'Hyderabad', 32000),

('Telangana', 'Warangal', 28000),

('Telangana', 'Nalgonda', 18000),

('Telangana', 'RangaReddy', 28000),

('Telangana', 'Medak', 21000),

('Telangana', 'Khammam', 18000),

('AndhraPradesh', 'Guntur', 19000),

('AndhraPradesh', 'Guntur', 19000),

('AndhraPradesh', 'Vizag', 22000),

('AndhraPradesh', 'Vijaywada', 18000),

('AndhraPradesh', 'Srikakulam', 16000),

('AndhraPradesh', 'Amaravathi', 16000),

('AndhraPradesh', 'Ananthpur', 32000),

('TamilNadu', 'Kanyakumari', 17000),

('TamilNadu', 'Kanyakumari', 17000),

('TamilNadu', 'Chennai', 19000),

('TamilNadu', 'Coimbatore', 22000);

GO

As we can see, there are duplicate rows available in this table.

Look at the different ways how we can remove the duplicate rows from the table.

First of all we need to identify whether we have any duplicate entries in the table that we can do by using the below query:

SELECT

SalesGroup

,DistrictName

,SalesAmount

,COUNT(*) AS NoOfRows

FROM

dbo.DistrictSales

GROUP BY

SalesGroup

,DistrictName

,SalesAmount

HAVING COUNT(*) > 1

Result:

From the result we can identify that there are duplicate rows in the table.

Now, we will look at the way how we can remove duplicates from the table:

WITH CTE AS

(

SELECT

SalesGroup

,DistrictName

,SalesAmount

,ROW_NUMBER() OVER(PARTITION BY SalesGroup, DistrictName, SalesAmount ORDER BY SalesID) AS Duplicates

FROM

dbo.DistrictSales

)

DELETE

FROM

CTE

WHERE

Duplicates <> 1

Now if you fire the below query again you won't find any duplicate rows in DistrictSales table:

SELECT

SalesGroup

,DistrictName

,SalesAmount

,COUNT(*) AS NoOfRows

FROM

dbo.DistrictSales

GROUP BY

SalesGroup

,DistrictName

,SalesAmount

HAVING COUNT(*) > 1