Re: I need help creating a query
От | Marcin Mank |
---|---|
Тема | Re: I need help creating a query |
Дата | |
Msg-id | 0bf301c6a769$b857bc00$0c67a8c0@maniek обсуждение исходный текст |
Ответ на | I need help creating a query ("Sergio Duran" <sergioduran@gmail.com>) |
Ответы |
Re: I need help creating a query
|
Список | pgsql-general |
----- Original Message ----- From: "Sergio Duran" <sergioduran@gmail.com> To: <pgsql-general@postgresql.org> 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
В списке pgsql-general по дате отправления: