remove-duplicates

--ways to remove duplicates data


--drop table #tech
--drop table #unique
create table #tech (id int identity (1,1),name varchar(50))

insert into #tech values ('C#')
insert into #tech values ('C#')
insert into #tech values ('C++')
insert into #tech values ('C++')
insert into #tech values ('F#')
insert into #tech values ('VB')
insert into #tech values ('C#')
insert into #tech values ('C#')

--#1 using temp table
select distinct name into #unique from #tech 
delete from #tech
insert into #tech select * from #unique
select * from #tech

--#2 using ROW_NUMBER()
--select * from (select *, ROW_NUMBER() OVER(partition by name order by name ) as rn from #tech) dups

delete dups 
from (select *, ROW_NUMBER() OVER(partition by name order by name ) as rn from #tech) dups
where rn >1


--#3 using CTE
;with cte
as (select ROW_NUMBER() OVER(partition by name order by (select 0 )) as rn from #tech)
delete from cte where rn > 1
select * from #tech