> 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.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
