Message-ID: <45F96A04.7020307@numerixtechnology.de>
Date: Thu, 15 Mar 2007 15:45:08 +0000
From: T E Schmitz <mailreg@numerixtechnology.de>
Reply-To: pgsql-sql@postgresql.org
User-Agent: Debian Thunderbird 1.0.7 (X11/20051017)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: join table with itself?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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