Jasmin Bertovic wrote:
>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
>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
If you try to tranlsate your Query into Unordered Set terms it will
"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 :)