How to get 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 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)

Is it intended that you have 10-45 in the second row? Where do you take a 10 from? — Quassnoi, Mar 11 '09 at 13:52
some of the calcs are not consistent... row2's (45-23) is row3-row2, but row9's (0-45) is row9-row3, should it not be (10-0) which is row17-row9? — MatBailie, Mar 11 '09 at 13:57
It seems you want to count the difference between the adjacent values in your original rowset (2, 3, 17, 9). Then I'll have to ask you: what is the column your ORIGINAL rowset is sorted on? Remember that there is no such thing as 'default ordering' in SQL. — Quassnoi, Mar 11 '09 at 14:28

MatBailie · Accepted Answer · 2020-09-11T09:52:39.350

64

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

edited Sep 11 '20 at 09:52

answered Mar 11 '09 at 13:50

MatBailie

83,401
18
103
137

1

If he has an index on rowInt (which I think he does) then my query is more efficient. If he doesn't, then your one is (if it's possible to talk about efficiency in this case). Look here for details: http://stackoverflow.com/questions/590079/for-autoincrement-fields-maxid-vs-top-1-id-order-by-id-desc – Quassnoi Mar 11 '09 at 14:09
1

I created a dummy table with 1 million randomly created records and found that both queries took the same time. In fact the only difference in execution plans was the position of a SORT function (I sorted the final output) – MatBailie Mar 11 '09 at 15:01

score 28 · Answer 2 · answered Mar 11 '09 at 13:50

28

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

answered Mar 11 '09 at 13:50

Quassnoi

413,100
91
616
614

score 18 · Answer 3 · edited Aug 07 '17 at 23:06

18

SQL 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 #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 row in respect to the given order in "over" clause.

edited Aug 07 '17 at 23:06

ChrisW

9,151
1
20
34

answered Oct 04 '16 at 19:50

Ansonmus

325
2
9

1

LAG. Not LAD. It won't let me edit because you have to change 6 characters – danmiser Apr 03 '17 at 20:33
This is the best solution. Other answers are ingenuitive, but this is efficient. – DonkeyKong Sep 13 '18 at 00:19
@DonkeyKong - Other answers reflect that the question is for SQL Server 2005, which does not support Analytic/Windowed Functions. – MatBailie Sep 11 '20 at 09:46

score 12 · Answer 4 · answered Mar 12 '09 at 01:34

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.ID

You 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, 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

score 4 · Answer 5 · answered Mar 12 '09 at 01:39

4

Does SQL Server support analytic functions?

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

answered Mar 12 '09 at 01:39

David Aldridge

51,479
8
68
96

1

Msg 195, Level 15, State 10, Line 3 'lag' is not a recognized built-in function name. – dance2die Mar 12 '09 at 01:40
1

Ive given this a point because yes Denali does...although maybe sql server didnt when this answer was posted – Pixelated Sep 14 '11 at 19:55
2

@FairFunk I've subtracted a point because the question is tagged 2005 so Denali functionality is irrelevant. – Martin Smith Oct 08 '11 at 12:17

score 1 · Answer 6 · edited Oct 08 '11 at 11:40

1

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

edited Oct 08 '11 at 11:40

LPL

16,827
6
51
95

answered Nov 23 '09 at 12:02

mns

11
1

score 1 · Answer 7 · edited May 27 '16 at 17:26

1

Query to Find the date difference between 2 rows of a single 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

edited May 27 '16 at 17:26

Thomas G

9,886
7
28
41

answered May 27 '16 at 17:09

arun sivaraman menon

21
1

2

This is a very old question and your answer doesn't really answer the same question posed. – shawnt00 May 27 '16 at 17:44

score 1 · Answer 8 · answered Jul 22 '19 at 11:38

I'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 FLOAT
AS
BEGIN
    DECLARE @Diff FLOAT
    IF @p1 > @p2 SET @Diff = @p1 - @p2 ELSE SET @Diff = @p2 - @p1
    RETURN @Diff
END

In a query to get the difference between column a and b:

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

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

8 Answers8

Linked