David.Chu.ca David.Chu.ca - 2 months ago 8
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
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...