sql - 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