CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Gaurav Badyal
Gaurav Badyal

Reputation: 21

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)

Upvotes: 0

Views: 2321

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

In SQL Server 2008, I would cross apply. The ordering of the rows is not 100% clear, but it seems based on diff:

select t.diff - t2.diff
from t cross apply
     (select top 1 t2.*
      from t t2
      where t2.diff < t.diff
      order by t2.diff desc
     ) t2;

Upvotes: 2

TheGameiswar
TheGameiswar

Reputation: 28890

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

Upvotes: 2

Related Questions