SQL Tips by Namwar Rizvi

May 15, 2007

Removing Duplicates Rows

Filed under: New Features,SQL Server 2005,tips,TSQL — namwar @ 8:01 PM

Removing duplicates is one of the most common TSQL issues faced by many programmers. Although there are several approaches to solve this but by using Row_Number() function in SQL Server 2005 we can remove duplicates easily. Following is the sample code to achieve this:

–Disable intermediate messages
Set nocount on

—Declare a sample table variable
Declare @m_SampleTable table
(
id int,
cityName varchar(50)

)

—Insert some sample data
Insert into @m_SampleTable (id,cityName) values (1,‘London’)
Insert into @m_SampleTable (id,cityName) values (2,‘London’)
Insert into @m_SampleTable (id,cityName) values (3,‘NewYork’)
Insert into @m_SampleTable (id,cityName) values (4,‘NewYork’)
Insert into @m_SampleTable (id,cityName) values (5,‘NewYork’)
Insert into @m_SampleTable (id,cityName) values (6,‘Karachi’)

–Display the data without removing duplicates
Select * from @m_SampleTable order by cityName


—Delete all those rows which have row number > 1 which means every group will have only one member remaining after this delete operation

Delete @m_SampleTable where
id in

(
–Selecting id of duplicate records
Select A.id from
(
Select Row_Number() Over(Partition by cityName Order by cityName) RowNum,id from @m_SampleTable
) A
Where A.RowNum>1

)

–Display result after removing the duplicates
Select * from @m_SampleTable order by cityName

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: