Re: [GENERAL] I need help creating a query

[Q]

On 15/07/2006, at 2:07 AM, Sergio Duran wrote: 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?  Try this: SELECT w2.* FROM (  SELECT name,             MAX(startdate) AS startdate         FROM worker         GROUP BY name     )     AS w1         JOIN worker AS w2         ON (w1.name = w2.name         AND w1.startdate = w2.startdate); Obviously you would use a real primary key instead of 'name' for the join constraint but you get the idea  --  Seeya...Q                -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-                           _____  /  Quinton Dolan - [EMAIL PROTECTED]   __  __/  /   /   __/   /      /            /    __  /   _/    /      /        Gold Coast, QLD, Australia   __/  __/ __/ ____/   /   -  /            Ph: +61 419 729 806                     _______  /                             _\