Re: compare two rows in the same column
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.
Subject
Written By
Posted
September 15, 2009 05:42PM
September 15, 2009 05:50PM
Re: compare two rows in the same column
October 16, 2009 11:15AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.