I have a table like this:
rowInt Value2 233 4517 109 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 Diff2 23 22 --45-233 45 -35 --10-459 0 -45 --0-4517 10 10 -- 10-0....The table is in SQL 2005 (Miscrosoft)
Best Answer
SELECT[current].rowInt,[current].Value,ISNULL([next].Value, 0) - [current].ValueFROMsourceTable AS [current]LEFT JOINsourceTable 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...
SELECTRowInt,Value,LEAD(Value, 1, 0) OVER (ORDER BY RowInt) - ValueFROMsourceTableSELECT rowInt, Value,COALESCE((SELECT TOP 1 ValueFROM myTable miWHERE mi.rowInt > m.rowIntORDER BYrowInt), 0) - Value AS diffFROM myTable mORDER BYrowIntSQL Server 2012 and up support LAG / LEAD functions to access the previous or subsequent row. SQL Server 2005 does not support this (in SQL2005 you need a join or something else).
A SQL 2012 example on this data
/* Prepare */select * into #tmpfrom(select 2 as rowint, 23 as Valueunion select 3, 45union select 17, 10union select 9, 0) x/* The SQL 2012 query */select rowInt, Value, LEAD(value) over (order by rowInt) - Value from #tmpLEAD(value) will return the value of the next row in respect to the given order in "over" clause.
If you really want to be sure of orders, use "Row_Number()" and compare next record of current record (take a close look at "on" clause)
T1.ID + 1 = T2.IDYou are basically joining next row with current row, without specifying "min" or doing "top". If you have a small number of records, other solutions by "Dems" or "Quassanoi" will work fine.
with T2 as (select ID = ROW_NUMBER() over (order by rowInt),rowInt, Valuefrom myTable)select T1.RowInt, T1.Value, Diff = IsNull(T2.Value, 0) - T1.Valuefrom ( SELECT ID = ROW_NUMBER() over (order by rowInt), *FROM myTable ) T1left join T2 on T1.ID + 1 = T2.IDORDER BY T1.IDDoes SQL Server support analytic functions?
select rowint,value,value - lag(value) over (order by rowint) difffrom myTableorder by rowint/select t1.rowInt,t1.Value,t2.Value-t1.Value as difffrom (select * from myTable) as t1,(select * from myTable where rowInt!=1union all select top 1 rowInt=COUNT(*)+1,Value=0 from myTable) as t2where t1.rowInt=t2.rowInt-1Query to Find the date difference between 2 rows of a single column
SELECTColumn name,DATEDIFF((SELECT MAX(date) FROM table name WHERE Column name < b. Column name),Column name) AS days_since_lastFROM table name AS bI'd just make a little function for that. Toss in the two values you need to know the difference between and have it subtract the smaller from the larger value. Something like:
CREATE FUNCTION [dbo].[NumDifference] ( @p1 FLOAT,@p2 FLOAT )RETURNS FLOATASBEGINDECLARE @Diff FLOATIF @p1 > @p2 SET @Diff = @p1 - @p2 ELSE SET @Diff = @p2 - @p1RETURN @DiffENDIn a query to get the difference between column a and b:
SELECT a, b, dbo.NumDifference(a, b) FROM YourTable