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