Re: [GENERAL] I need help creating a query

[Dann Corbit]

The query date supplied should be applied against start date and then take the minimum record from that set.   From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Sergio Duran Sent: Thursday, July 13, 2006 12:20 PM To: pgsql-general@postgresql.org 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 Maybe I only need some insight here, maybe it should be done with 2 queries, maybe it should be done with functions, I don't know. Thanks