Subtract value from previous row in mysql

Question

I have table with the following structure

ID  VALUE
1   100
2   200
3   300
4   400
5   500

I want output like this

ID  VALUE  DIFF_to_Prev
1   100     0
2   200    100 
3   300    100 
4   400    100
5   500    100

This is the query I've tried so far

SET @LastVALUE:= 0;
SET @LastSN:= 0;

SELECT dtr.SN, dtr.VALUE, 
       IF(@LastSN = dtr.SN, dtr.Value - @LastVALUE, 0) DIFF_to_Prev     
    FROM difftworows as dtr

This is the results I get from it:

ID      VALUE   DIFF_to_Prev
1       100     0
2       200     0
3       300     0
4       400     0
5       500     0

I want to know what I'm doing wrong. Please tell me how can I fix it with suggestions.

Thanks!!

Answer 1

Of course you a getting 0 in this column, you are not giving the parameter any value..

If the ID's are continuously and 3 will always be previous of 4 , and 4 of 5 and ETC... then it can be done with a join:

SELECT t.id,t.value,t.value-coalese(s.value,0) as DIFF_to_Prev
FROM YourTable t
LEFT OUTER JOIN YourTable s ON(t.id = s.id + 1)

Answer 2

E.g.:

SELECT x.*
     , COALESCE(x.value-@prev,0) diff_to_prev
     , @prev:=value  
  FROM my_table x
     , (SELECT @prev:=null) vars 
 ORDER 
    BY id;