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
