Re: Table transform query

["Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch>]

> 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 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 
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