gaurav badyal gaurav badyal - 3 months ago 11
SQL Question

How to compare data of two rows and put difference in other column?

Diff abc
row1 3 xyz
row2 5 xyz
row3 10 xyz
row3 13 xyz
row3 15 xyz


And I want compare of two row's column like (row2.Diff - row1.Diff) into any other column.

Result
2 (r2.diff - r1.diff)
5 (r3.diff - r2.diff)
3 (r4.diff - r3.diff)
2 (r5.diff - r4.diff)

Answer

answers specific to sql server

;with cte
as
(
select *,row_number() over (order by diff) as rownum
from #tmp
)
select 
c1.*,c2.diff-c1.diff as result
from cte c1
left join
cte c2 on c1.rownum+1=c2.rownum

if using SQLserver2012 or above..

select *,isnull(lead(diff) over (order by diff)-diff,0) as difff
from #tmp