mysql - Difference tabels based on Id and unixtimestamp -

mysql - Difference tabels based on Id and unixtimestamp -

my table looking folowing:

unix_timestap id value 1351058019 1 500 1351058029 1 505 1351058039 9 105 1351058049 9 200 1351076620 1 520

i able generate new column contain differences between values per id between current value , first available "past" value. past mean unixtimestamp not placed in order in original table.

the output be:

unix_timestap id value difference 1351058019 1 500 0 1351058029 1 505 5 1351058039 9 105 0 1351058049 9 200 95 1351076620 1 520 15

if no previous unix_timestamp exists, value should zero.

a hint/tip much appreciated. thanks

if solution still needed

select t3.unix_timestamp, t3.id, t3.value, ifnull(t3.value - t5.value, 0) diff test1 t3 bring together ( select t1.unix_timestamp, t1.id, max(t2.unix_timestamp) old_stamp `test1` t1 left bring together test1 t2 on t1.id = t2.id , t1.unix_timestamp > t2.unix_timestamp grouping t1.unix_timestamp, t1.id) t4 on t3.unix_timestamp = t4.unix_timestamp , t3.id = t4.id left bring together test1 t5 on t4.old_stamp = t5.unix_timestamp , t4.id = t5.id

mysql unix-timestamp