> 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.
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
casehere.
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
f.serial,
f.date as dateL,
( SELECT MIN(f2.date) FROM foo AS f2 WHERE f2.serial = f.serial AND f2.date > f.date AND f2.delivery = 'R'
) AS dateR
FROM foo AS f
WHERE f.delivery = 'L'
ORDER BY f.serial, f.date
-- Self-join
SELECT
t1.serial,
t1.date as dateL,
MIN(t2.date) as dateR
FROM foo t1
LEFT JOIN foo 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
ORDER BY t1.serial, t1.date