Re: I need help creating a query

How about if we make it simpler, only 1 table

create table worker(
name varchar(50),
position varchar(50),
startdate date,
salary numeric(9,2));
insert into worker values ('Jon', 'boss', '2001-01-1', 1000.00);
insert into worker values ('Peter', 'cleaning', '2002-01-01', 100.00);
insert into worker values ('Peter', 'programming', '2004-01-01', 300.00);
insert into worker values ('Peter', 'management', '2006-01-01', 500.00);
select * from worker;
name | position | startdate | salary
-------+-------------+------------+---------
Jon | boss | 2001-01-01 | 1000.00
Peter | cleaning | 2002-01-01 | 100.00
Peter | programming | 2004-01-01 | 300.00
Peter | management | 2006-01-01 | 500.00

I want to group by name, order by date desc and show the first grouped
salary, maybe I should write an aggregate function that saves the first
value and ignores the next ones. Is there already an aggregate function that
does this? I havent written any aggregate functions yet, can anybody spare
some pointers?

On 7/14/06, Sergio Duran <sergioduran(at)gmail(dot)com> wrote:
>
> 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.
> >
>
>