SELECT statement not working as intended

From: Rich Shepard <rshepard(at)appl-ecosys(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: SELECT statement not working as intended
Date: 2011-10-10 22:17:23
Message-ID: alpine.LNX.2.00.1110101512201.11484@salmo.appl-ecosys.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2011-10-10 22:19:58 Re: SELECT statement not working as intended
Previous Message Rafal Pietrak 2011-10-10 21:02:04 Re: a dumb question regarding RULES