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
Recent Comments