Remove duplicate rows from a table in SQL Server

Hello Friends,

I have tried to remove the duplicate rows from the table using row_number function. Here I am explaining the demo using the temp table.

Steps:

1. Create Temp Table or Select your table

Create Table #Main

(

id int,

item varchar(100)

)

2. Insert some records

insert into #main values (119,1)

insert into #main values (119,2)

insert into #main values (119,2)

insert into #main values (119,3)

insert into #main values (119,3)

insert into #main values (119,4)

insert into #main values (119,5)

insert into #main values (119,6)

insert into #main values (119,7)

insert into #main values (119,8)

insert into #main values (119,8)

insert into #main values (119,8)

3. expected Output

–Output

–119 1

–119 2

–119 3

–119 4

–119 5

–119 6

–119 7

–119 8

4. first we have start to get single records

–we got those record which count =1

select id,item from

(

select *,

(select count(item) as NoOfCount from #Main where item =Mst.Item group by id,item) as NCount

from #Main Mst

)a

where NCount =1

5. Result 1

–Result

–119  1

–119  4

–119  5

–119  6

–119  7

6. Now find the records where item count is >1

–we got those record which count >1

select id,item from

(

select id,item,ROW_NUMBER()Over(Partition by Item Order by Item) As Rep

from

(

select *,

(select count(item) as NoOfCount from #Main where item =Mst.Item group by id,item) as NCount

from #Main Mst

)a where NCount>1

)RepT where rep=1

7. Result 2

–Result

–119  2

–119  3

–119  8

8. Result 1 Union Result 2

9. Output

–Result

–119  1

–119  4

–119  5

–119  6

–119  7

–119  2

–119  3

–119  8

Thanks,

Http://www.ibusiness-management.com

Advertisements

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