Re: I need help creating a query

Ok, all the suggestions were good.

I think I'll stick with Marcin Mank's query for now, I'll also try to work
further with Richard Broersma's query later.

Thank you guys, you were really helpful.

On 7/14/06, Marcin Mank <marcin(dot)mank(at)gmail(dot)com> wrote:
>
>
> ----- Original Message -----
> From: "Sergio Duran" <sergioduran(at)gmail(dot)com>
> To: <pgsql-general(at)postgresql(dot)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
>
>