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

sql, sql-server, sql-server-2005, t-sql

Solution

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

Problem

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)

Original source

Related problems