o/p shud be      1,a,10,10
			1,a,5,-5
			1,a,15,5
			1,a,20,10
			and so on.....

declare @tbl as table(id int identity(1,1),groups varchar(5),value int)
insert into @tbl
select 'a',10 union all
select 'a',5 union all
select 'a',15 union all
select 'a',20 union all
select 'b',10 union all
select 'b',100

I have came up with this

select * from @tbl t1
outer apply(select 
                   top 1(groups),value
                   from  @tbl t2 
					where t1.id=t2.id+1 and t1.groups=t2.groups and ???? --- this part i cant understand on how to get first value from each group

				order by groups desc)t3

select t1.*,t1.value-coalesce(t3.value,0) as yourresult from @tbl t1
outer apply(select top 1  value
                   from  @tbl t2 
					where t1.groups=t2.groups 
				order by id asc)t3

quote:

---

Originally posted by visakh16

i think this is enough

select t1.*,t1.value-coalesce(t3.value,0) as yourresult from @tbl t1
outer apply(select top 1  value
                   from  @tbl t2 
					where t1.groups=t2.groups 
				order by id asc)t3

---