MYSQL Calculate Column value based on a Case Statement and other Row Values -

i new mysql apologies question, not sure start.

i have mysql table looks below;

table_1

record_id   price   price_difference    new_value 1           100     null 2           101     1 3           115     14 4           135     20 5           95      -40 6           80      -15 

what want add , entry record 1 of null since there no price difference.

for record 2 want 'new_value' price difference value of 14 divided value dependent on price in previous record 1.

e.g. 14/@var_1

where

@var_1 = (case  when price_1 < 50 0.5 when price_1 between 50.001 , 100 1 when price_1 between 100.001 , 150 1.25 when price_1 between 150.001 , 150  1.4 ....etc. end case 

i presume can done in mysql , ideas how can go it?

this can done joining 2 aliases of same table, whereby 1 points current record_id , other points previous record_id adding 1 in join condition.

this can work if rows record_id guaranteed sequential no intervening gaps.

select   pcur.price,   pcur.price_difference,    case      when plast.price < 50 (pcur.price_difference / 0.05)     when plast.price between 50.001 , 100 (pcur.price_difference / 1)     when plast.price between 100.001 , 150 (pcur.price_difference / 1.25)     when plast.price between 150.001 , 200 (pcur.price_difference / 1.4)     else 999 # don't forget else case   end var_1   /* first aliased table current record */   prices pcur   /* second aliased table previous one, id 1 behind */   left join prices plast on (pcur.record_id = plast.record_id + 1); 

here's demo: http://sqlfiddle.com/#!2/552ee/7

a slower method work if record_id have potential gaps in sequence, using subselect retrieve price previous row via order by , limit 1. have not-super performance on large dataset.

select    rid,   price,   price_difference,   case      when var_1 < 50 (price_difference / 0.05)     when var_1 between 50.001 , 100 (price_difference / 1)     when var_1 between 100.001 , 150 (price_difference / 1.25)     when var_1 between 150.001 , 200 (price_difference / 1.4)     else 999 # don't forget else case   end var_1 (   select     record_id rid,     price,      price_difference,    /* subselect returns 1 price previous record_id may not in sequence */    (select price prices record_id < rid order record_id desc limit 1) var_1   prices ) pcalc 

and here's demo of one: http://sqlfiddle.com/#!2/552ee/5