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.