----- Original Message ----- From: "Sergio Duran" <sergioduran@xxxxxxxxx> To: <pgsql-general@xxxxxxxxxxxxxx> Sent: Thursday, July 13, 2006 9:20 PM Subject: [GENERAL] I need help creating a query > Hello, > > I need a little help creating a query, I have two tables, worker and > position, for simplicity sake worker only has its ID and its name, position > has the ID of the worker, the name of his position, a date, and his salary/ > > worker: worker_id, name > position: position_id, worker_id, position, startdate, salary > > If I perfom a query joining both tables, I can obtain all the workers and > the positions the've had. > > SELECT name, startdate, position, salary FROM worker JOIN position > USING(worker_id); > worker1 | 2001-01-01 | boss | 999999 > worker2 | 2001-01-01 | cleaning | 100 > worker2 | 2006-04-01 | programmer | 20000 > worker2 | 2006-07-04 | management | 25000 > > so far so good, now I need to obtain all the workers only with the position > they had on a given date. > if I wanted to know the positions on '2006-05-01' it would return > worker1 | 2001-01-01 | boss | 999999 > worker2 | 2006-04-01 | programmer | 20000 > This should work: select distinct on(W.worker_id) W.name,P.position,P.salary from worker W,position P where P.worker_id=W.worker_id and 'SOME DATE' >= P.startdate order by W.worker_id,P.startdate Cheers Marcin
