Re: Table transform query

Rodrigo De León skrev:
> On 9/18/07, Philippe Lang <philippe(dot)lang(at)attiksystem(dot)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