From: Nis Jørgensen <nis@superlativ.dk>
To: pgsql-sql@postgresql.org
Subject: Re: Table transform query
Date: Wed, 19 Sep 2007 10:13:08 +0200
Message-ID: <fcqlnd$9u3$1@sea.gmane.org> (raw)
In-Reply-To: <a55915760709180651p4df78cfm59dc219bdd6729ce@mail.gmail.com>
References: <6C0CF58A187DA5479245E0830AF84F4218CF1F@poweredge.attiksystem.ch>
<a55915760709180651p4df78cfm59dc219bdd6729ce@mail.gmail.com>
Rodrigo De León skrev:
> On 9/18/07, Philippe Lang <philippe.lang@attiksystem.ch> wrote:
>> ... into this:
>>
>>
>> serial dateL dateR
>> --------------------
>> 1 1 2
>> 1 4
>> 2 1 2
>> 3 1 3
>> 4 2 3
>> 5 3
>
> SELECT t1.serial, t1.DATE AS datel, t2.DATE AS dater
> FROM t t1 LEFT JOIN t t2 ON( t1.serial = t2.serial
> AND t1.DATE < t2.DATE)
> WHERE t1.delivery = 'L'
> AND ( t2.delivery = 'R'
> OR t2.delivery IS NULL)
> ORDER BY t1.serial
This only works if (serial, delivery) is unique - which it doesn't
appear to be, from the solution posted by Philippe himself (which does a
LIMIT 1 in the subquery).
A take on a self-join:
SELECT t1.serial, t1.date as dateL, MIN(t2.date) as dateR
FROM t t1 LEFT JOIN t t2
ON t1.serial = t2.serial AND t1.date < t2.date AND t2.delivery = 'R'
WHERE t1.delivery = 'L'
GROUP BY t1.serial, t1.date
Whether this is any clearer, or runs faster, than the correlated
subquery (which could be simplified by using MIN instead of LIMIT 1) is
up for debate and test, respectively.
Nis
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: nis@superlativ.dk
Subject: Re: Table transform query
In-Reply-To: <fcqlnd$9u3$1@sea.gmane.org>
* 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