Re: compare two rows in the same column

[Gavin Taylor]

It's probably faster without a join; how about:

========

mysql> select * from test.fred;
+------------+---------+
| date | iChange |
+------------+---------+
| 2009-01-02 | 4 |
| 2009-01-03 | 5 |
| 2009-01-04 | -8 |
| 2009-01-05 | -6 |
| 2009-01-06 | 17 |
+------------+---------+
5 rows in set (0.01 sec)


mysql> SET @prev_val=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT date, iChange-@prev_val as diff, @prev_val:=iChange AS iChange FROM test.fred ORDER BY date ASC;

+------------+------+---------+
| date | diff | iChange |
+------------+------+---------+
| 2009-01-02 | 4 | 4 |
| 2009-01-03 | 1 | 5 |
| 2009-01-04 | -13 | -8 |
| 2009-01-05 | 2 | -6 |
| 2009-01-06 | 23 | 17 |
+------------+------+---------+
5 rows in set (0.00 sec)

=========

Note that the order of columns matter; for obvious reasons you can't calculate "diff" after setting @prev_val.

<edit... schoolboy error in my 1st attempt, forgot the ":" in "@prev_val:=iChange">



Edited 1 time(s). Last edit at 10/16/2009 11:18AM by Gavin Taylor.