Re: I need help creating a query

The query date supplied should be applied against start date and then
take the minimum record from that set.

________________________________

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Sergio Duran
Sent: Thursday, July 13, 2006 12:20 PM
To: pgsql-general(at)postgresql(dot)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