MySQL: Performing an operation on different rows based on condition?

Question

Say I have a query which returns the following result:

| val   |  type  | i |
----------------------
| 59    |   1    | 1 |
| 40    |   2    | 2 |
| 12    |   1    | 3 |
| 3     |   2    | 4 |
| 24    |   1    | 5 |
| 30    |   1    | 6 |
| 98    |   2    | 7 |
| 45    |   2    | 8 |
| 46    |   1    | 9 |

val = an arbitrary number
type = 1 or 2
i = auto increment column, just for reference

I want to perform the following subtraction:

A - B

A:  Value of row of type '2'
B:  Value of row above A of type '1' if it exists, but without crossing a row of type '2'

I really hope that made sense..

Some examples:

Row i=2 is of type 2.  So the first row above it of type 1 is i=1.  
So the subtraction will be:  40 - 59 = -19 

Row i=7 is of type 2.  So the first row above it of type 1 is i=6
So the subtraction will be: 98 - 30 = 68

Row i=8 is of type 2.  But if we go above we cross a value of type 2.
So the subtraction should return 0, or be ignored, whichever is simplest.

In the end the result should return a column of values where type=1 and the subtracted value .

Eg:

| val | diff |
--------------
| 59  | -19  |
| 12  | -9   |
| 24  | 0    |  ***
| 30  | 68   |
| 46  | 0    |  ***

*** Return diff=0 if the subtraction was never made for this row.

I have the query for getting my initial table. I know how to do the subtraction (in the real case val is a date, but I'm keeping it simple for this example).

The only thing I have no idea how to do is perform logic based on different rows in MySQL. For example how can I find the nearest row based on some condition and then reference that row to perform some operation on it? Is there perhaps a way to reference the current 'i' and do an subrtaction on (row at i) minus (row at i-1) or something similar.

I've spent many hours on this already and I'm just about to give up and do it in php instead and take the performance hit. As a last resort I'm asking here if there's a way to do all this in mysql directly?

I don't need a full solution as this is pretty complex requirement but I will take any sort of advice or pointers you can give me. I'm not the best at MySQL so maybe I'm missing something simple.

Thanks.

Answer 1

 SELECT t1.val AS val, IFNULL( t2.val, t1.val ) - t1.val AS diff FROM my_table AS t1
 LEFT JOIN my_table AS t2 ON( t2.type = 2 AND t2.i = t1.i + 1 )
 WHERE t1.type = 1 ORDER BY t1.i;

Tested on your data, result:

| val | diff |
--------------
| 59  | -19  |
| 12  | -9   |
| 24  | 0    |
| 30  | 68   |
| 46  | 0    |