Re: Single Table Select With Aggregate Function

Rich Shepard <rshepard(at)appl-ecosys(dot)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