Jefry Ihsana Jefry Ihsana - 7 months ago 17
PHP Question

How to get previous record from mysql

col1 | col2 | col3 | col4 |
------------------------------------
a | 1 | 1 | 2 |
b | 2 | 2 | 6 |
a | 3 | 3 | 12 |


col4 = col2+col3+prev record col4

I can not solve the problem using mysql, its so hard for me

So can anyone help me?

Answer

You could use a variable for calculating col4 = col2+col3+prev record col4:

select col1, col2, col3,
       @prev := col2+col3+@prev as col4
from   t,
       (select @prev := 0) init

SQL Fiddle

Note that to extend this you'd better define a sort order, as the above query will follow the insertion order, which in the long run might not correspond to what you want to do.

So for example, if col2 would define the sort order, add ORDER BY col2

Comments