Re: I need help creating a query

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

This query didn't give you the max salary. First, the subselect give your maximum start date for
each employee the occurred before your given date '2006-05-01', regardless if they get a raise or
a cut.

Then we join the result of the sub-select to the main table to get the specific records that meet
the criteria of the sub-select.

select W2.name, W1.position, W2.pdate, w1.salary
from worker as W1
join
(select name, max(startdate) as pdate
from worker
where startdate <= '2005-01-01'
group by name
) as W2
on (W1.name = W2.name) and (W1.startdate = W2.pdate)
;

name | position | pdate | salary
-------+-------------+------------+---------
Jon | boss | 2001-01-01 | 1000.00
Peter | programming | 2004-01-01 | 300.00

So with this query, we get what everyones salary would be on the date of '2005-01-01' regardless
of raises or cuts.

Regards,

Richard Broersma Jr.