declare @t table(id int,names varchar(10),[values] int) insert into @t select 79 ,'Aa',50 insert into @t select 52 ,'Bb',90 insert into @t select 123 ,'Cc',70 insert into @t select 975 ,'Bb',60 insert into @t select 5412 ,'Aa',57 insert into @t select 5452 ,'Cc',60 insert into @t select 57486,'Bb',70 insert into @t select 544 ,'Cc',70 insert into @t select 542 ,'Aa',102 insert into @t select 457 ,'Bb',50 delete t from @t t where exists(select 1 from @t where names=t.names and ([values]>t.[values] or ([values]=t.[values] and id>t.id)))select * from @t /* id names values ----------- ---------- ----------- 52 Bb 90 544 Cc 70 542 Aa 102 */
--search select t.* from tb t where not exists (select 1 from tb where names = t.name and (values > t.values or (values > t.values and id < t.id))) order by t.id--delete delete tb from tb t where not exists (select 1 from tb where names = t.name and (values > t.values or (values > t.values and id < t.id)))
declare @t table(id int,names varchar(10),[values] int)
insert into @t select 79 ,'Aa',50
insert into @t select 52 ,'Bb',90
insert into @t select 123 ,'Cc',70
insert into @t select 975 ,'Bb',60
insert into @t select 5412 ,'Aa',57
insert into @t select 5452 ,'Cc',60
insert into @t select 57486,'Bb',70
insert into @t select 544 ,'Cc',70
insert into @t select 542 ,'Aa',102
insert into @t select 457 ,'Bb',50 delete t from @t t where exists(select 1 from @t where names=t.names and ([values]>t.[values] or ([values]=t.[values] and id>t.id)))select * from @t
/*
id names values
----------- ---------- -----------
52 Bb 90
544 Cc 70
542 Aa 102
*/
select t.* from tb t where not exists (select 1 from tb where names = t.name and (values > t.values or (values > t.values and id < t.id))) order by t.id--delete
delete tb from tb t where not exists (select 1 from tb where names = t.name and (values > t.values or (values > t.values and id < t.id)))