Wednesday 1 May 2019

Delete rows with subquery

create table #test(id int,name varchar(200))

insert into #test
select 1,'Shabash '
union  all
select 1,'Shabash '
union  all
select 1,'Shabash '
union  all
select 1,'Shabash '
union  all
select 1,'Shabash '
union  all
select 1,'Shabash '
union  all
select 1,'Shabash '
union  all
select 1,'Shabash '
union all
select 1,'Shrikant'
union  all
select 1,'Shrikant'


alter table #test
add Row_id int identity(1,1)

--with subquery
delete a from #test a
where a.Row_id not in ( select  min (Row_id) from #test group by Id,name)

select * from #test

First Database In Sql Server