This makes sence, now that I have had some time to think about it. If I self join, I have to make sure that my data is continuous to shift all the rows by 1. Some of the things could have missing dates or maybe I could self join on an incremental counter using a tmp table. I have do go back to 'drawing board' and do some more thinking.
This is great info. Thanks again. --- Alexander Keremidarski <[EMAIL PROTECTED]> wrote: > Hi, > Jasmin Bertovic wrote: > <cut> > > >For example; > > > >SELECT number as current_day, (number - > <prevnumber>) > >as change_from_prev_day FROM TABLE ORDER BY DATE > > > ><prevnumber> is the reference that I need from the > >previous row. > > > >Am I missing something simple or do I have to do > this > >outside of MYSQL? > > > > > > 1. Yes you are missing something very basic (not > simple). > Tables in Relational Database Model are defined as > Sets of Items. I.e. > there is no Internal order of table. > Server is free to store and retrieve rows in any > order. > > ORDER BY clause is applied After rows are retrieved > and processed (row > by row) > > If you try to tranlsate your Query into Unordered > Set terms it will > sounds like: > > "For each member of set do something with it and > After that sort the > result according to ..." > But since Set has no order you can not say "For each > member use Prev member" > > 2. In your case there might be solution :) But it is > very specific. > Assuming date column is Primary Key you can just > join table to it self > > SELECT t1.date_col, t1.num, t2.date_col, t2.num) > FROM yourtable AS t1, > yourtable AS t2 WHERE t1.date_col = t2.date_col + > INTERVAL 1 DAY; > > Above is just for your info - to see what is > happening. Having all these > columns you can: > > SELECT t1.num as current, (t1.num - t2.num) as diff > FROM yourtable AS > t1, yourtable AS t2 WHERE t1.date_col = t2.date_col > + INTERVAL 1 DAY; > > The only trick is to deal with 1st date because > there is no previous, > but I will let this excercise to you :) > > > --------------------------------------------------------------------- > 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 > __________________________________________________ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com --------------------------------------------------------------------- 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
