Re: Single Table Select With Aggregate Function

Hi,

On 4 January 2012 10:26, Rich Shepard <rshepard(at)appl-ecosys(dot)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