Hi, There comes to mind another solution or have missed something?
set @prev_val := 0.0; Select colA-@prev_val, @prev_val := colA from tablename; Even adding an order by clause does not cause problem, Contrary to my belief that determining values for caluclated columns happen before ordering. Select colA-@prev_val, @prev_val := colA from tablename Order by Key1; works as well; Anvar. At 11:04 PM 29/04/2002 -0500, you wrote: >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 --------------------------------------------------------------------- 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
