Re: SELECT statement not working as intended

you are also grouping by sample date, those are the largest values for the
criteria you have set out in the group by.

On Mon, Oct 10, 2011 at 6:17 PM, Rich Shepard <rshepard(at)appl-ecosys(dot)com>wrote:

> I'm trying to query the table to extract the single highest value of a
> chemical by location and date. This statement gives me all the values per
> stream, site, and date:
>
> SELECT str_name, site_id, sample_date, max(quant) FROM chemistry WHERE
> hydro
> = 'Humboldt' group by str_name, sample_date, site_id order by str_name,
> site_id;
>
> I'm not seeing why this doesn't work; the top few lines of output are:
>
> CalifCrk | CalCrk | 1996-10-18 | 188
> CalifCrk | CalCrk | 1996-08-23 | 183
> CalifCrk | CalCrk | 1996-07-29 | 201
> CalifCrk | CalCrk | 1996-09-27 | 185
>
> when what I want is only the third line.
>
> A clue to the correct syntax is solicited. I'm sure it's something simple
> that I'm just not seeing.
>
> Rich
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>