To: pgsql-sql@postgresql.org
From: =?UTF-8?B?TmlzIErDuHJnZW5zZW4=?= <nis@superlativ.dk>
Subject: Re: Table transform query
Date: Wed, 19 Sep 2007 10:13:08 +0200
Lines: 39
Message-ID: <fcqlnd$9u3$1@sea.gmane.org>
References: <6C0CF58A187DA5479245E0830AF84F4218CF1F@poweredge.attiksystem.ch>
	<a55915760709180651p4df78cfm59dc219bdd6729ce@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
In-Reply-To: <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