Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f2CKJSx60180 for ; Mon, 12 Mar 2001 15:19:28 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2890Dx95292 for ; Thu, 8 Mar 2001 04:00:13 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f288Y6S07460 for pgsql-sql@postgresql.org; Thu, 8 Mar 2001 03:34:06 -0500 (EST) (envelope-from news) From: Salvador =?iso-8859-1?Q?Main=E9?= X-Newsgroups: comp.databases.postgresql.sql Subject: I need an aggregate with 2 parameters Date: Thu, 08 Mar 2001 08:31:19 -0100 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 32 Message-ID: <3AA75167.68073235@yahoo.com> Reply-To: salva@ronincoders.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org X-Mailer: Mozilla 4.75 [es] (X11; U; Linux 2.2.16-22 i586) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200103/229 X-Sequence-Number: 1888 Hello: I have a table with pluviometrical data meteo (rain float, day date, oid station) I want to select the the day of maximum value for each year for a given measurement station. It should look like : select max(rain),day from meteo group by date_part('year', day) where station=7654765; though it obiously doesn't work. I think that an aggregate function would be the best way to do this, because the table is quite large and I need indexes for date and also for stations, so adding a new one for the rain is too much. But the aggregate should look like: max_rain(rain, day) and return the day corresponding to the maximum rain, So the query would be something like: select max(rain), max_rain(rain, day) from meteo group by date_part('year', day) where station=47654765; I've tried to define a composite type for rain and day and the neccesary functions to make the aggregate, but I cannot find the correct way. Can someone help me? Is this sollution possible? -- Salvador Maine http://www.ronincoders.com