Calculating differences between rows 1

[natrons]

I am trying to figure out the best way to calculate differences between a value and the value in the previous row. I have come up with several possible ways to do this, but I don't have a lot of SQL experience and would be grateful for help from someone who has done this before.

Here is a sample of my data:
ID VALUE
-- -----
1 12
2 15
3 18
4 9
5 13

I would like to find the differences between the value in the current row and the value in the previous row. Something like this:

ID DIFF
-- ----
2 3
3 3
4 -9
5 4

It seems like I need to do some sort of recursive view or subquery and then get the difference between the two aliases of the table, one of which is one row behind the other.

Thanks for any advice!

Nathan Schultz

 

[crufty]

try:

select t2.id, t1.id, (t2.value-t1.value)
from table t1, table t2
where (t2.id-1) = t1.id

I left the id's in the select for clarity.

This assumes that you are interested in the difference between two consecutive id's, i.e. (2-1), (3-2), etc.

 

[natrons]

Thanks,

I love an easy solution. Sometimes I think too much. My problem is a little more complicated than this, but this is an elegant approach that I will use.

-Nathan

 

[bperry]

If you get into the situation where the ID values happen to be *not* consecutive, then you could use this solution:

select Id,
m1.Value -
(Select value from MyTable m2
where Id = (Select MAX(Id) from MyTable m3
where m3.Id < m1.Id)
)
as TheDiff
from MyTable m1

 

[logic4fun]

I went through the answer.. I was wondering what if it is this case and i want the difference

Here is a sample of my data:
ID Type VALUE
-- -----
1 10 12
1 8 15
4 10 18
4 15 9


in the above case i need the difference for each id betwen that two rows. such as in this here i want
ID Diff
1 3
4 -9

its like difference between the Type10 and its next row.

I know with clauses would help here but was wondering is there any way to do it without with.. just to learn

thanks