David.Chu.ca David.Chu.ca - 10 months ago 42
SQL Question

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

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 Source
   ISNULL([next].Value, 0) - [current].Value
   sourceTable       AS [current]
   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...