Re: Single Table Select With Aggregate Function - Mailing list pgsql-general

Rich Shepard <rshepard@appl-ecosys.com> writes:
>    What I need to do are three things:

>    1.) Find the date and site for the maximum value of a specified constituent
> on a named stream.

>    2.) Find the values of that same constituent at other sites on the named
> stream on that same date.

>    3.) Find the date of the maximum value of a constituent for all sites on
> the named stream.

You can do that type of thing using subqueries, eg

    select ... from mytab
      where col = (select max(col) from mytab where ...)

or if you don't mind a nonstandard construct, consider SELECT DISTINCT ON.
There's an example of how to use that in the SELECT reference page
(look for the weather-reports example).  In any case you're going to
have to consider what you want to have happen when there are multiple
occurrences of the maximum value.

            regards, tom lane