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