Thread: I need an aggregate with 2 parameters

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

This should work:

SELECT day, rain FROM meteo WHERE rain = (SELECT max(rain) FROM meteo
WHERE date_part('year', day) = '2001')

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Thu, 8 Mar 2001, Salvador [iso-8859-1] Main� wrote:

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

Salvador Mainé <salvamaine@yahoo.com> writes:
> I want to select the the day of maximum value for each year for a given
> measurement station.

This is exactly the sort of thing that SELECT DISTINCT ON is meant for.
See the "weatherReports" example in the SELECT man page,
http://www.postgresql.org/devel-corner/docs/postgres/sql-select.html
        regards, tom lane