From: T E Schmitz <mailreg@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: join table with itself?
Date: Thu, 15 Mar 2007 15:45:08 +0000
Message-ID: <45F96A04.7020307@numerixtechnology.de> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mailreg@numerixtechnology.de
Subject: Re: join table with itself?
In-Reply-To: <45F96A04.7020307@numerixtechnology.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox