subtraction from previous value in mysql -

i have table like

    id_indicator    value   trend   date_data 1   0   0   2011-08-18 09:16:15 1   2   1   2011-08-18 10:16:15 1   1   -1  2011-08-18 11:16:15 1   2   1   2011-08-18 12:16:15 2   21  0   2011-08-18 13:16:15 2   21  0   2011-08-18 14:16:15 2   21  0   2011-08-18 15:16:15 3   3   0   2011-08-18 16:16:15 3   4   1   2011-08-18 17:16:15 3   4   0   2011-08-18 18:16:15 4   4   0   2011-08-18 19:16:15 

i need find out difference between previous values based on id_indicator , add column in right , input value. example as

    id_indicator    value   trend   date_data   difference  1   0   0   2011-08-18 09:16:15 0 1   2   1   2011-08-18 10:16:15 2 1   1   -1  2011-08-18 11:16:15 -1 1   2   1   2011-08-18 12:16:15 1 2   21  0   2011-08-18 13:16:15 0 2   21  0   2011-08-18 14:16:15 0 2   21  0   2011-08-18 15:16:15 0 3   3   0   2011-08-18 16:16:15 0 3   4   1   2011-08-18 17:16:15 1 3   4   0   2011-08-18 18:16:15 0 4   4   0   2011-08-18 19:16:15 0 

thanks

you first create lookup gives previous timestamp each id , date , join lookup.

something (untested):

select * -- t1.value - t2.vaue...   (select t1.id_indicator,           t1.date_data,           max(t2.date_date) prev_date_data    table t1    left join table t2 on t1.id_indicator = t2.id_indicator    , t2.date_data < t1.date_data    group 1, 2) lookup left join table t1 on t1.id_indicator = lookup.id_indicator , t1.date_data = lookup.date_data left join table t2 on t2.id_indicator = lookup.id_indicator , t2.date_data = lookup.prev_date_data