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
