How to Remove Duplicate Rows from a Table in SQL Server

Introduction

It is obvious that if somehow duplicate rows get inserted into a table, it becomes a major issue to delete those duplicate rows. So this topic will help us to delete those duplicate rows from the specific table.

Background

I used some basic T-SQL (Transact-SQL*) code to accomplish the problem. So you don’t need to worry understand the codes and even you can use just blindly.

Problem

I had a database table with duplicate rows and I wanted to get rid of it. So this is how I solved my issue.

Solution

First, say I have a table named Stores with following column.

Table_Stores

So my first select query:

SELECT * FROM Stores

And Output:

SelectQuery

Now look carefully in above figure, Stores table does contain duplicate records. Check first two columns marked with rad box. Every the columns are the same except Id.

Now let’s find out duplicate rows in a way so that we can easily understand. We are now going to add an extra column in a temporary table, let’s name it RowNumber. RowNumber will hold count of duplicate rows.

So let’s execute the following query:

WITH Temp AS
(
SELECT row_number() OVER (PARTITION BY
Slno
,Region
,Territory
,Town
,ShopName
,ShopAddress
,ShopType
,Slab
,Phone
,DmsCode
,Latitude
,Longitude ORDER BY DmsCode) AS RowNumber, *
FROM Stores
)SELECT * FROM Temp

You see, I haven’t include Id, since it’s not duplicate.

SelectQuery

Now if you look above table RowNumber column, you will able to know which records contain duplicate values on rows with RowNumber greater than 1.

Now if we want to keep unique values in the table and delete others execute following query:

WITH Temp AS
(
SELECT row_number() OVER (PARTITION BY
Slno
,Region
,Territory
,Town
,ShopName
,ShopAddress
,ShopType
,Slab
,Phone
,DmsCode
,Latitude
,Longitude
ORDER BY DmsCode) AS RowNumber, *
FROM nps_new.dbo.Stores
)
DELETE from Temp WHERE RowNumber >1
SELECT * FROM Stores ORDER BY DmsCode

If the query executes, we will find only unique values in the table.

Disclaimer:

I did not explain the queries as I assumed that people who deals with such thing already know SQL.

Advertisements

2 Comments

  1. To get The Duplicate Latitude and Longitude

    WITH Dup_LatLong AS
    (
    SELECT row_number() OVER (PARTITION BY
    LATITUDE, LONGITUDE ORDER BY Rpropid1) AS RowNumber, UniqueValue, convert(Varchar(50),LATITUDE)+convert(Varchar(50),LONGITUDE) Check_Cond
    FROM YourTable)
    Select UniqueValue,LATITUDE, LONGITUDE, convert(Varchar(50),LATITUDE)+convert(Varchar(50),LONGITUDE)
    from YourTable
    where convert(Varchar(50),LATITUDE)+convert(Varchar(50),LONGITUDE) in (Select Check_Cond from Dup_LatLong where RowNumber > 1)
    order by 7

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s