try:
------------------------------------------------------
create table aa (s varchar(20),i int)
insert into aa
select 'a001' ,100
union
select 'a001',50
union
select 'a001',20
union
select 'b001',200
union
select 'b001',20select identity(int,1,1) as id,* into #t from aaselect
a.s,
i = case when a.id=(select min(id) from where s=a.s) then a.i else 0 end
from
#t a
order by
a.s,a.i
------------------------------------------------------
create table aa (s varchar(20),i int)
insert into aa
select 'a001' ,100
union
select 'a001',50
union
select 'a001',20
union
select 'b001',200
union
select 'b001',20select identity(int,1,1) as id,* into #t from aaselect
a.s,
i = case when a.id=(select min(id) from where s=a.s) then a.i else 0 end
from
#t a
order by
a.s,a.i
union all
select s,0 from #1 c where not exists(select 1 from
(select id from #1 a where not exists(select 1 from #1 where a.s=s and a.id>id)) b where c.id=b.id)
insert into aa
select 'a001' ,100
union
select 'a001',50
union
select 'a001',20
union
select 'b001',200
union
select 'b001',20select *,identity(int,1,1) as id into #1 from aaselect s,i from #1 a where not exists(select 1 from #1 where a.s=s and a.id>id)
union all
select s,0 from #1 c where not exists(select 1 from
(select id from #1 a where not exists(select 1 from #1 where a.s=s and a.id>id)) b where c.id=b.id)
order by s,i descs i
-------------------- -----------
a001 20
a001 0
a001 0
b001 20
b001 0
insert into #aa
select 'a001' ,100
union
select 'a001',50
union
select 'a001',20
union
select 'b001',200
union
select 'b001',20select identity(int,1,1) as id,* into #t from #aaselect * from #tselect
a.s,
i_new = (case when a.id=(select min(id) from #t where s=a.s) then a.i else 0 end)
from
#t a
order by
a.s,a.i