Subtracting one row of data from another in SQL

Question

I've been stumped with some SQL where I've got several rows of data, and I want to subtract a row from the previous row and have it repeat all the way down.

So here is the table:

CREATE TABLE foo (
  id,
  length
)

INSERT INTO foo (id,length) VALUES(1,1090)
INSERT INTO foo (id,length) VALUES(2,888)
INSERT INTO foo (id,length) VALUES(3,545)
INSERT INTO foo (id,length) VALUES(4,434)
INSERT INTO foo (id,length) VALUES(5,45)

I want the results to show a third column called difference which is one row subtracting from the one below with the final row subtracting from zero.

+------+------------------------+
| id   |length |  difference  |
+------+------------------------+
|    1 | 1090  |  202         |
|    2 |  888  |  343         |
|    3 |  545  |  111         |
|    4 |  434  |  389         |
|    5 |   45  |   45         |

I've tried a self join but I'm not exactly sure how to limit the results instead of having it cycle through itself. I can't depend that the id value will be sequential for a given result set so I'm not using that value. I could extend the schema to include some kind of sequential value.

This is what I've tried:

SELECT id, f.length, f2.length, (f.length - f2.length) AS difference
FROM foo f, foo f2

Thank you for the assist.

Answer 1

So they are just ordered largest to smallest?

SELECT f.id, f.length, (f.length - ISNULL(t.length, 0)) AS difference
FROM foo AS f
LEFT JOIN (
    SELECT f1.id
        ,MAX(f2.length) as length
    FROM foo AS f1
    INNER JOIN foo AS f2
        ON f1.length > f2.length
    GROUP BY f1.id
) AS t -- this is the triangle
    ON t.id = f.id

You can use COALESCE (or IFNULL) instead of ISNULL for MySQL.

Answer 2

What about something like this:

SELECT T2.ID, T2.[Length], T2.[Length]-T1.[Length] AS 'Difference'
FROM Foo AS T1 RIGHT OUTER JOIN Foo AS T2 ON ( T1.ID = (T2.ID-1) )
ORDER BY T1.ID

Answer 3

edit: fixed when re-read Q (misunderstood)

SELECT f.id, 
       f2.id, 
       f.length, 
       f2.length, 
       (f.length -f2.length) AS difference
FROM foo f, 
     foo f2 
where f2.id = f.id+1

id was ambiguous

edit: note: tested in mysql 5.0

Answer 4

Select f1.id, f1.seqnum, f2.seqnum, f1.length, f2.length, f1.length-f2.length 

From (

Select Id, length, row_number(order by length) 'seqnum'
From
foo

) f1

Inner join (

Select 
Id, length, row_number(order by length) 'seqnum' from foo union select 0, 0, 0

) f2 

On f1.seqnum = f2.seqnum + 1

Order by f1.length desc

Answer 5

This might help you (somewhat).

select a.id, a.length, 
coalesce(a.length - 
    (select b.length from foo b where b.id = a.id + 1), a.length) as diff
from foo a

Answer 6

Yipee!!! this does the trick:

SELECT  f.id, f.length, 
    (f.length - ISNULL(f2.length,0)) AS diff
FROM foo f
LEFT OUTER JOIN foo f2
ON  f2.id = (f.id +1)

Please check for other cases also, it is working for the values you posted! Note this is for SQL Server 2005