Thread: join table with itself?

I am not getting to grips with the following query:

set-up: Postgresql 8.1

The table HISTORY contains stockmarket data:
DAY        HIGH    LOW        
2007/02/28    6286.1    6166.2    
2007/02/27    6434.7    6270.5
2007/02/26    6446.8    6401.5

I'd like to produce the following result:
DAY        HIGH    LOW    DAYS2FALL    HIGHEST
where DAYS2FALL is the number of days it takes for LOW to fall below the 
present row's LOW
where HIGHEST is the highest HIGH during that period.


I had a stab at DAYS2FALL:

SELECT  present.day, present.low, (MIN(future.day)-present.day) as 
days2fall FROM history AS present,history AS future
WHERE    present.day < future.day AND    future.low <= present.low
GROUP BY present.day,present.low
ORDER BY days2fall DESC

but didn't manage to express HIGHEST. Also, my attempt isn't exactly the 
fastest.
-- 


Regards,

Tarlika Elisabeth Schmitz

On 15/03/07, T E Schmitz <mailreg@numerixtechnology.de> wrote:--
William Leite Araújo
Analista de Banco de Dados - QualiConsult

William Leite Araújo wrote:
> On 15/03/07, *T E Schmitz* <mailreg@numerixtechnology.de 
> <mailto:mailreg@numerixtechnology.de>> wrote:
> (...)
>  
>    Try join the tables.
> 
>     SELECT  present.day, present.low, (MIN(future.day)-present.day) as
> 
>     days2fall FROM history AS present JOIN history AS future ON (
>     present.day < future.day AND
>          future.low <= present.low )
>     GROUP BY present.day,present.low
>     ORDER BY days2fall DESC

That produces the same result as my previous example but maybe the join 
is more efficient, Thank you for the suggestion.

However, I am still stuck as to how to retrieve HIGHEST. The result set 
produced by the above query only contains those tuples whose LOW is 
lower than present.LOW.

For HIGHEST, I need to look at the rows between present.day and DAYS2FALL:

something like

SELECT MAX (high) from history WHERE day >= present.day AND day < 
(present.day + days2fall)

-


Regards,

Tarlika Elisabeth Schmitz