List:General Discussion« Previous MessageNext Message »
From:Alexander Keremidarski Date:May 2 2002 5:46pm
Subject:Re: User Variables and Previous Row Question
View as plain text  
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 :)

Thread
User Variables and Previous Row QuestionJasmin Bertovic2 May
  • Re: User Variables and Previous Row QuestionVictoria Reznichenko2 May
  • Re: User Variables and Previous Row QuestionAlexander Keremidarski2 May
    • Re: User Variables and Previous Row QuestionJasmin Bertovic2 May