pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedI need an aggregate with 2 parameters
3+ messages / 3 participants
[nested] [flat]
* I need an aggregate with 2 parameters
@ 2001-03-08 09:31 Salvador Mainé <salvamaine@yahoo.com>
2001-03-12 21:08 ` Re: I need an aggregate with 2 parameters Michael Fork <mfork@toledolink.com>
2001-03-12 23:20 ` Re: I need an aggregate with 2 parameters Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Salvador Mainé @ 2001-03-08 09:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: I need an aggregate with 2 parameters
2001-03-08 09:31 I need an aggregate with 2 parameters Salvador Mainé <salvamaine@yahoo.com>
@ 2001-03-12 21:08 ` Michael Fork <mfork@toledolink.com>
1 sibling, 0 replies; 3+ messages in thread
From: Michael Fork @ 2001-03-12 21:08 UTC (permalink / raw)
To: salva@ronincoders.com; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: I need an aggregate with 2 parameters
2001-03-08 09:31 I need an aggregate with 2 parameters Salvador Mainé <salvamaine@yahoo.com>
@ 2001-03-12 23:20 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2001-03-12 23:20 UTC (permalink / raw)
To: salva@ronincoders.com; +Cc: pgsql-sql
Salvador =?iso-8859-1?Q?Main=E9?= <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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-03-12 23:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-08 09:31 I need an aggregate with 2 parameters Salvador Mainé <salvamaine@yahoo.com>
2001-03-12 21:08 ` Michael Fork <mfork@toledolink.com>
2001-03-12 23:20 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox