How to get difference between two rows for a column field?

Question

I have a table like this:

rowInt  Value
2       23
3       45
17      10
9       0
....

The column rowInt values are integer but not in a sequence with same increament. I can use the following sql to list values by rowInt:

SELECT * FROM myTable ORDER BY rowInt;

This will list values by rowInt. How can get get the difference of Value between two rows with the result like this:

rowInt   Value Diff
2        23    22    --45-23
3        45    -35   --10-45
9        0     -45   --0-45
17       10    10    -- 10-0
....

The table is in SQL 2005 (Miscrosoft)

Answer 1

SELECT rowInt, Value,
       COALESCE(
       (
       SELECT TOP 1 Value
       FROM myTable mi
       WHERE mi.rowInt > m.rowInt
       ORDER BY
             rowInt
       ), 0) - Value AS diff
FROM  myTable m
ORDER BY
      rowInt

Answer 2

SELECT
   [current].rowInt,
   [current].Value,
   ISNULL([next].Value, 0) - [current].Value
FROM
   sourceTable       AS [current]
LEFT JOIN
   sourceTable       AS [next]
      ON [next].rowInt = (SELECT MIN(rowInt) FROM sourceTable WHERE rowInt > [current].rowInt)

EDIT: Thinking about it, using a subquery in the select (ala Quassnoi's answer) may be more efficient. I would trial different versions, and look at the execution plans to see which would perform best on the size of data set that you have...

Answer 3

If you really want to be sure of orders, use "Row_Number()" and compare next record of current record (take a close look at "on" clause)

T1.ID + 1 = T2.ID

You are basically joining next row with current row, without specifying "min" or doing "top". If you have a small number of records, other solutions by "Dems" or "Quassanoi" will work fine.

with T2 as (
    select ID = ROW_NUMBER() over (order by rowInt),
      rowInt, Value
    from myTable
)
select  T1.RowInt, T1.Value, Diff = IsNull(T2.Value, 0) - T1.Value
from    ( SELECT ID = ROW_NUMBER() over (order by rowInt), *
      FROM myTable ) T1
     left join T2 on T1.ID + 1 = T2.ID
ORDER BY T1.ID

Answer 4

Does SQL Server support analytic functions?

select   rowint,
         value,
         value - lag(value) over (order by rowint) diff
from     myTable
order by rowint
/

Answer 5

select t1.rowInt,t1.Value,t2.Value-t1.Value as diff from (select * from myTable )as t1,

(select * from myTable where rowInt!=1 union all select top 1 rowInt=COUNT(*)+1,Value=0

from myTable)as t2 where t1.rowInt=t2.rowInt-1