From: | "Sergio Duran" <sergioduran(at)gmail(dot)com> |
---|---|
To: | "Richard Broersma Jr" <rabroersma(at)yahoo(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: I need help creating a query |
Date: | 2006-07-14 15:48:52 |
Message-ID: | c44353520607140848y25ab2ce5x8eccb6db5ed7bf81@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Nice, Richard, but you use max(startdate), how about the salary? i cant use
max(salary) how about if he got a pay cut?
My current solution is to write the nested query on the field list, like
SELECT worker.*,
(select salary
FROM position where worker_id=worker.worker_id
and fecha<='2006-04-01' LIMIT 1) as salary
FROM worker;
but I can only return 1 column from that subquery and repeating the same
subquery for each column needed (position, date and salary) seems a little
too much, if I write a procedure would postgres would optimize the access?
On 7/13/06, Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:
>
> >
> > 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 is just a quick guess. I am not sure if the logic is correct but it
> could be a starting
> point.
>
> select P2.worker_id, P2.pdate, P1.position, P1.salary
> from position as P1
> join
> (select worker_id, max(startdate) as pdate
> from position
> where startdate <= '2006-05-01'
> group by worker_id, position_id) as P2
> on (P1.worker_id = P2.worker_id) and (P1.startdate = P2.pdate)
> ;
>
> Regards,
>
> Richard Broersma Jr.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Sergio Duran | 2006-07-14 16:07:40 | Re: I need help creating a query |
Previous Message | Tom Lane | 2006-07-14 15:48:25 | Re: apparent wraparound |