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

On Oct 6, 2011, at 9:34, Rich Shepard  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.  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.

> 
> Rich
> 

David J.