From: | "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | "Tomi N/A" <hefest(at)gmail(dot)com>, "pgsql general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: a question on SQL |
Date: | 2006-12-12 17:17:00 |
Message-ID: | CA896D7906BF224F8A6D74A1B7E54AB319875D@JENMAIL01.ad.intershop.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Seems that a recursive use of "DISTINCT ON" will do it:
create table factories (id int, factory varchar(10), ownerid int);
create table products (id int, product varchar(10), atime int
,factory_id int);
--owner 1 : factory 1
insert into products values(1,'p1',123,1);
insert into products values(2,'p2',124,1);
insert into products values(3,'p3',125,1);
--owner 1 : factory 2
insert into products values(4,'p4',1,2);-- expected
--owner 2 : factory 3
insert into products values(5,'p5',127,3);-- expected
insert into products values(6,'p6',128,3);
insert into products values(7,'p7',129,3);
insert into factories values(1,'f1',1);
insert into factories values(2,'f2',1);
insert into factories values(3,'f3',2);
select distinct on (foo.ownerid)
foo.ownerid,foo.factory,foo.atime
from
(select distinct on (f.ownerid, p.factory_id)
f.ownerid,factory,atime
from factories f,products p
where p.factory_id=f.id
order by f.ownerid, p.factory_id, atime
)foo
order by foo.ownerid, foo.atime
Cheers,
Marc
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-12-12 17:19:56 | Re: resetting sequence to cur max value |
Previous Message | Ragnar | 2006-12-12 17:13:47 | Re: a question on SQL |