Content-Class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Table transform query
Date: Tue, 18 Sep 2007 15:49:47 +0200
Message-ID: <6C0CF58A187DA5479245E0830AF84F4218CF20@poweredge.attiksystem.ch>
Thread-Topic: [SQL] Table transform query
thread-index: Acf59ZVMHKWd76BHSS2ChG5uHC6axgABAV8Q
References: <6C0CF58A187DA5479245E0830AF84F4218CF1F@poweredge.attiksystem.ch>
From: "Philippe Lang" <philippe.lang@attiksystem.ch>
To: <pgsql-sql@postgresql.org>

pgsql-sql-owner@postgresql.org wrote:

> Hi,
>=20
> I'm trying to find out how to transform this kind of table data
> (history of rental data in a firm):=20

...    =20

I have answred my own question: yes, there is a pure SQL solution, with
a subselect:

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


-------------
select=20

f.serial,=20
f.date as dateL,
( select f2.date from foo as f2=20
  where f2.serial =3D f.serial=20
  and f2.date > f.date=20
  and f2.delivery =3D 'R'=20
  order by f2.date asc=20
  limit 1
) as dateR

from foo as f
where f.delivery =3D 'L'
order by f.serial, f.date
-------------


I'm not sure if we could use a self-join here...


Cheers,


Philippe Lang