If you have a primary key which is an autoincrement field then the
following works.
CREATE TABLE `tab1`
(
Key1 smallint(5) unsigned NOT NULL auto_increment,
ColA float default NULL,
PRIMARY KEY (`Key1`)
)
TYPE=MyISAM;
insert into tab1 values (1, 3.4), (2,4.6), (3, 3.1), (4,8.2), (5,6.4);
Select * from tab1;
mysql> Select * from tab1;
+------+------+
| Key1 | ColA |
+------+------+
| 1 | 3.4 |
| 2 | 4.6 |
| 3 | 3.1 |
| 4 | 8.2 |
| 5 | 6.4 |
+------+------+
5 rows in set (0.00 sec)
create temporary table t1
select Key1+1 as prev, colA
from tab1
order by Key1;
select key1,
(tab1.colA - t1.colA) as Delta
from tab1 inner join t1
on (tab1.key1 = t1.prev)
order by key1;
+------+-------------------+
| key1 | Delta |
+------+-------------------+
| 2 | 1.19999980926514 |
| 3 | -1.5 |
| 4 | 5.09999990463257 |
| 5 | -1.79999971389771 |
+------+-------------------+
4 rows in set (0.00 sec)
Gordon Bruce
A US MySQL Training Partner
> -----Original Message-----
> From: Nissim Lugasy [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 24, 2002 2:57 PM
> To: [EMAIL PROTECTED]
> Subject: delta between rows?
>
> To Mysql Team
>
> how can I generate a list of deltas between columns in different rows
for
> the entire table?
> what I need is an sql command that does something like this:
> for N =0 to i do : select "colA of current rowN" - "colA of pervious
> row(N-1)" from tab1;
>
> ColA = floating point number.
>
> Thanks
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <mysql-unsubscribe-
> [EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
