On Oct 6, 2011, at 9:34, Rich Shepard <rshepard@xxxxxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
