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    [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...

EDIT2:

I still see this garnering votes, though it's unlikely many people still use SQL Server 2005.

If you have access to Windowed Functions such as LEAD(), then use that instead...

SELECT   RowInt,   Value,   LEAD(Value, 1, 0) OVER (ORDER BY RowInt) - Value FROM   sourceTable 

Answer 2

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