Re: a question on SQL

["Marc Mamin" <M.Mamin@xxxxxxxxxxxx>]

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