Content-Class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Table transform query
Date: Fri, 21 Sep 2007 10:25:36 +0200
Message-ID: <6C0CF58A187DA5479245E0830AF84F4218CF26@poweredge.attiksystem.ch>
Thread-Topic: [SQL] Table transform query
thread-index: Acf6lRinZ5FTruZGRtCVYdaqAbIBHgBkvwTw
References: <6C0CF58A187DA5479245E0830AF84F4218CF1F@poweredge.attiksystem.ch>
	<a55915760709180651p4df78cfm59dc219bdd6729ce@mail.gmail.com>
	<fcqlnd$9u3$1@sea.gmane.org>
From: "Philippe Lang" <philippe.lang@attiksystem.ch>
To: =?iso-8859-1?Q?Nis_J=F8rgensen?= <nis@superlativ.dk>,
	<pgsql-sql@postgresql.org>


> A take on a self-join:
>=20
> SELECT t1.serial, t1.date as dateL, MIN(t2.date) as dateR FROM t t1
> LEFT JOIN t t2 ON t1.serial =3D t2.serial AND t1.date < t2.date AND
> t2.delivery =3D 'R' =20
> WHERE t1.delivery =3D 'L'
> GROUP BY t1.serial, t1.date
>=20
> 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. =20

Hi Nis,

Thanks for your tip with the "MIN" operator.


I always imagined a self-join solution was faster than a query with a =
subselect. With a quick test, it seems to be the case here.


CREATE TABLE foo (
    serial integer,
    delivery character(1),
    date integer
);

INSERT INTO foo (serial, delivery, date) VALUES (1, 'L', 1); INSERT INTO =
foo (serial, delivery, date) VALUES (2, 'L', 1); INSERT INTO foo =
(serial, delivery, date) VALUES (3, 'L', 1); INSERT INTO foo (serial, =
delivery, date) VALUES (1, 'R', 2); INSERT INTO foo (serial, delivery, =
date) VALUES (2, 'R', 2); INSERT INTO foo (serial, delivery, date) =
VALUES (4, 'L', 2); INSERT INTO foo (serial, delivery, date) VALUES (5, =
'L', 3); INSERT INTO foo (serial, delivery, date) VALUES (3, 'R', 3); =
INSERT INTO foo (serial, delivery, date) VALUES (4, 'R', 3); INSERT INTO =
foo (serial, delivery, date) VALUES (1, 'L', 4);


-- Subselect
SELECT=20
f.serial,
f.date as dateL,
( SELECT MIN(f2.date)
  FROM foo AS f2
  WHERE f2.serial =3D f.serial
  AND f2.date > f.date
  AND f2.delivery =3D 'R'=20
) AS dateR
FROM foo AS f
WHERE f.delivery =3D 'L'
ORDER BY f.serial, f.date

-- Self-join
SELECT=20
t1.serial,=20
t1.date as dateL,=20
MIN(t2.date) as dateR=20
FROM foo t1=20
LEFT JOIN foo t2=20
ON t1.serial =3D t2.serial=20
AND t1.date < t2.date=20
AND t2.delivery =3D 'R'
WHERE t1.delivery =3D 'L'
GROUP BY t1.serial, t1.date
ORDER BY t1.serial, t1.date