Hi, On 4 January 2012 10:26, Rich Shepard <rshep...@appl-ecosys.com> wrote: > select max(quant), site, sampdate from chemistry where stream = 'SheepCrk' > and param = 'TDS' group by site, sampdate; > > but this gives me the value of each site and date, not the maximum for all > dates at a specific site. Postgres tells me that both site and sampdate must > be in the 'group by' clause. > > Obviously the syntax is more complex than I thought it would be and I > would like a pointer to the correct way to write these queries.
window functions might be helpful: http://www.postgresql.org/docs/9.1/static/tutorial-window.html p4=> select * from public.chemistry order by sampdate, site, quant desc; quant | site | sampdate -------+------+------------ 100 | 1 | 2012-01-03 80 | 1 | 2012-01-03 30 | 1 | 2012-01-03 400 | 2 | 2012-01-03 100 | 2 | 2012-01-03 30 | 2 | 2012-01-03 100 | 3 | 2012-01-03 80 | 3 | 2012-01-03 30 | 3 | 2012-01-03 80 | 4 | 2012-01-03 (10 rows) p4=> select distinct first_value(quant) over (partition by sampdate, site order by quant desc), sampdate, site from public.chemistry order by site; first_value | sampdate | site -------------+------------+------ 100 | 2012-01-03 | 1 400 | 2012-01-03 | 2 100 | 2012-01-03 | 3 80 | 2012-01-03 | 4 (4 rows) -- Ondrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
