CODEWITHSUNDEEP
sql-servert-sql
juniorDev
juniorDev

Reputation: 155

Subtract For Sequential Row ID's in SQL Server

I want to calculate between difference value of sequencel rows. For example, if exists rn and one low rn (row number 4 and 5), that will be calculated.

Can you give me any idea about that?

SS

Upvotes: 0

Views: 212

Answers (2)

Ben Thul
Ben Thul

Reputation: 32707

Take a look at LAG. It goes something like this:

select [value] - lag([value], 1, 0) 
    over (partition by [LagValue] order by [rn] desc)
from your_table

Upvotes: 0

thepirat000
thepirat000

Reputation: 13114

You can do it with a CTE (assuming SQL 2005+) and ROW_NUMBER():

;With Rows
As 
(
    Select *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As RowNum 
    From Lag
)
Select R1.LagValue, R1.Value, R1.rn, R1.rn - IsNull(R2.rn, 0) As Difference
From Rows R1
Left Join Rows R2 On R1.RowNum = R2.RowNum + 1

Upvotes: 1

Related Questions