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

On Oct 5, 2011, at 19:34, 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

"Max" is an aggregate function and thus requires one of:
1) GROUP BY
2) "Window" - max(quant) OVER (PARTITION BY ...)

To be present in the query.

A correlated sub-select would work but you would still need to use group by and you would not gain anything in this particular scenario.

They each have their own usage scenarios and your description is not sufficient to determine which one you need; but likely adding an appropriate GROUP BY clause will get you what you want.

Also, while the warning only specifies the param field all the other field will give you the same error if you use the GROUP BY claus. The Window syntax affects just the aggregate function and so only that single "field" would need to be modified BUT the window clause results in all records remaining in the final query whereas the GROUP BY clause effectively removes duplicates.

David J.