On Wed, Oct 5, 2011 at 7:34 PM, Rich Shepard <rshepard@xxxxxxxxxxxxxxx> wrote:
A table (chemistry) has columns named site_id, sample_date, param, quant,
and str_name (among other columns). I want to find the site_id, sample_date,
and quant for a specific str_name and param. I cannot get the proper syntax
in the SELECT statement.
My attempts are variations of,
SELECT max(quant), param, site_id, sample_date, str_name from chemistry
WHERE param = 'TDS' AND str_name = 'BurrowCrk';
which prompts postgres to tell me,
ERROR: column "chemistry.param" must appear in the GROUP BY clause or be
used in an aggregate function
I suspect that retrieving these data requires nested SELECT statements,
and I'd appreciate learning how to retrive such data.
Rich
Based on your subject line, I'm guessing that you want something like this:
select quant, param, site_id, sample_date, str_name
from chemistry
where param = 'TDS' and str_name = 'BurrowCrk'
and quant = (select max(quant) from chemistry where param = 'TDS' and str_name = 'BurrowCrk')
