How to get the 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 rowInt column values ​​are integers, but not in sequence with the same magnification. I can use the following sql to list rowInt values:

 SELECT * FROM myTable ORDER BY rowInt; 

The rowInt values ​​will be listed here. How to get the difference of values ​​between two rows with the result as follows:

 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 select (ala Quassnoi) may be more efficient. I would study the different versions and look at the execution plans to see which ones would best fit the size of the data set that you have ...

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 

Answer 3

If you really want to be sure of your orders, use "Row_Number ()" and compare the next record of the current record (look carefully at the "on" clause)

 T1.ID + 1 = T2.ID 

Basically you join the next line with the current line without specifying "min" or "top". If you have a small number of records, other solutions from Dems or Quassanoi will work fine.

 with T2 as ( select ID = ROW_NUMBER() over (order by rowInt), rowInt, Value from myTable ) select T1.RowInt, T1.Value, Diff = IsNull(T2.Value, 0) - T1.Value from ( SELECT ID = ROW_NUMBER() over (order by rowInt), * FROM myTable ) T1 left join T2 on T1.ID + 1 = T2.ID ORDER BY T1.ID 

Answer 4

SQL Server 2012 and above support LAG / LEAD functions to access the previous or next row. SQL Server 2005 does not support this (in SQL2005 you need a connection or something else).

SQL 2012 example on this data

 /* Prepare */ select * into #tmp from ( select 2 as rowint, 23 as Value union select 3, 45 union select 17, 10 union select 9, 0 ) x /* The SQL 2012 query */ select rowInt, Value, LEAD(value) over (order by rowInt) - Value from #tmp 

LEAD (value) will return the value of the next line relative to the specified order in the over clause.

Answer 5

Does SQL Server support analytic features?

 select rowint, value, value - lag(value) over (order by rowint) diff from myTable order by rowint / 

Answer 6

 select t1.rowInt,t1.Value,t2.Value-t1.Value as diff from (select * from myTable) as t1, (select * from myTable where rowInt!=1 union all select top 1 rowInt=COUNT(*)+1,Value=0 from myTable) as t2 where t1.rowInt=t2.rowInt-1 

Answer 7

Query to find the difference between two rows of the same column

 SELECT Column name, DATEDIFF( (SELECT MAX(date) FROM table name WHERE Column name < b. Column name), Column name) AS days_since_last FROM table name AS b 

Answer 8

I would just do a little function for this. Throw in two values, you need to know the difference between them and subtract the smaller from the larger value. Something like:

 CREATE FUNCTION [dbo].[NumDifference] ( @p1 FLOAT, @p2 FLOAT ) RETURNS FLOAT AS BEGIN DECLARE @Diff FLOAT IF @p1 > @p2 SET @Diff = @p1 - @p2 ELSE SET @Diff = @p2 - @p1 RETURN @Diff END 

In the query, to get the difference between columns a and b:

 SELECT a, b, dbo.NumDifference(a, b) FROM YourTable