Обсуждение: Selecting All Columns Associated With Maximum Value of One Column

   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

On Oct 5, 2011, at 19:34, Rich Shepard <rshepard@appl-ecosys.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
particularscenario. 

They each have their own usage scenarios and your description is not sufficient to determine which one you need; but
likelyadding 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
GROUPBY claus.  The Window syntax affects just the aggregate function and so only that single "field" would need to be
modifiedBUT the window clause results in all records remaining in the final query whereas the GROUP BY clause
effectivelyremoves duplicates. 

David J.

On Wed, 5 Oct 2011, Chris Curvey wrote:

> 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')

Chris,

   Thank you. I missed seeing the latter part.

   This returns 0 rows, but it puts me on the right track.

Rich

On Wed, 5 Oct 2011, David Johnston wrote:

> "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.

David,

   I was unaware of the windows functions. I see the document page for 9.0.5
so I'll carefully read that and upgrade from 9.0.4 (which may also have this
function; I'll check).

Thanks,

Rich

On Wed, 5 Oct 2011, David Johnston wrote:

> 2) "Window" -  max(quant) OVER (PARTITION BY ...)

   Hmm-m-m. I have a problem here emulating the example on the document page.
Regardless of which column is first after SELECT postgres tells me that
column does not exist.

select site_id, sample_date, param, max(quant) over (partition by param)
chemistry;
ERROR:  column "site_id" does not exist
LINE 1: select site_id, sample_date, param, max(quant) over (partiti...
                ^
select str_name, site_id, sample_date, param, max(quant) over (partition by
str_name) chemistry;
ERROR:  column "str_name" does not exist
LINE 1: select str_name, site_id, sample_date, param, max(quant) ove...
                ^
   What am I doing incorrectly here?

   Also, with the window function can I limit the output to a single str_name
and param?

Rich

On Oct 6, 2011, at 9:34, Rich Shepard <rshepard@appl-ecosys.com> wrote:

> On Wed, 5 Oct 2011, David Johnston wrote:
>
>> 2) "Window" -  max(quant) OVER (PARTITION BY ...)
>
>  Hmm-m-m. I have a problem here emulating the example on the document page.
> Regardless of which column is first after SELECT postgres tells me that
> column does not exist.
>
> select site_id, sample_date, param, max(quant) over (partition by param)
> chemistry;
> ERROR:  column "site_id" does not exist
> LINE 1: select site_id, sample_date, param, max(quant) over (partiti...

Missing the FROM before chemistry

>               ^
> select str_name, site_id, sample_date, param, max(quant) over (partition by
> str_name) chemistry;
> ERROR:  column "str_name" does not exist
> LINE 1: select str_name, site_id, sample_date, param, max(quant) ove...
>               ^
>  What am I doing incorrectly here?
>
>  Also, with the window function can I limit the output to a single str_name
> and param?

Not directly.  After you create the windowed result you can turn it into a sub-query and filter that.  The other form
suggested(where quant = select max(quant) from chemistry where ...) is probably a better performer though for your need
-I did't read the subject line closely enough and my two options don't directly give you what you are looking for.
Thoughera ing the window query and then adding a (where quant_max = quant) clause would get you closer; quant_max being
thecolumn alias for the window expression. 

>
> Rich
>

David J.

On Thu, 6 Oct 2011, David Johnston wrote:

> Missing the FROM before chemistry

   D'oh! Obviously not yet sufficiently cafinated this morning.

>>  Also, with the window function can I limit the output to a single str_name
>> and param?
>
> Not directly.  After you create the windowed result you can turn it into a
> sub-query and filter that.  The other form suggested (where quant = select
> max(quant) from chemistry where ...) is probably a better performer though
> for your need - I did't read the subject line closely enough and my two
> options don't directly give you what you are looking for.  Though era ing
> the window query and then adding a (where quant_max = quant) clause would
> get you closer; quant_max being the column alias for the window
> expression.

   Ah.

Thanks, David,

Rich

Rich Shepard, 06.10.2011 15:13:
> I was unaware of the windows functions. I see the document page for 9.0.5
> so I'll carefully read that and upgrade from 9.0.4 (which may also have this
> function; I'll check).

Windowing functions are available since 8.4

On Thu, 6 Oct 2011, David Johnston wrote:

Missing the FROM before chemistry

  D'oh! Obviously not yet sufficiently cafinated this morning.
[...]

You just infringed my patent on making unprovoked stupid mistakes in posts to the Pg mailing lists!

I thought I had a protected monopoly on appearing stupid in these posts.

[MORE SERIOUSLY]
Such mistakes are so easy to make!  One knows what one has written, so one does not check adequately!

Recently I made 3 silly mistakes in about five lines, but fortunately, someone was able to decode what I had written and said it was useful.

(Patent trolling is becoming really big business, so it would not surprise me if someone actually patented a sophisticated version of the patent I pretended to have above!)


Cheers,
Gavin