Search Postgresql Archives

Re: I need help creating a query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.mank@xxxxxxxxx> wrote:

----- 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux