Re: Selecting All Columns Associated With Maximum Value of One Column

On Wed, Oct 5, 2011 at 7:34 PM, Rich Shepard <rshepard(at)appl-ecosys(dot)com>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')