Message-ID: <45F99675.5050801@numerixtechnology.de>
Date: Thu, 15 Mar 2007 18:54:45 +0000
From: T E Schmitz <mailreg@numerixtechnology.de>
Reply-To: mailreg@numerixtechnology.de
User-Agent: Debian Thunderbird 1.0.7 (X11/20051017)
MIME-Version: 1.0
To: =?ISO-8859-1?Q?William_Leite_Ara=FAjo?= <william.bh@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: join table with itself?
References: <45F96A04.7020307@numerixtechnology.de>
	<bc63ad820703150857h2ff195b0wac18476730b19c3c@mail.gmail.com>
In-Reply-To: <bc63ad820703150857h2ff195b0wac18476730b19c3c@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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