| From: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Single Table Select With Aggregate Function |
| Date: | 2012-01-03 23:26:46 |
| Message-ID: | alpine.LNX.2.00.1201031518070.18032@salmo.appl-ecosys.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
I'm probably not seeing the obvious so I keep making the same mistake. The
table holds water chemistry data from multiple streams, sites within each
stream, sampling dates, and many chemical constituents.
What I need to do are three things:
1.) Find the date and site for the maximum value of a specified constituent
on a named stream.
2.) Find the values of that same constituent at other sites on the named
stream on that same date.
3.) Find the date of the maximum value of a constituent for all sites on
the named stream.
Thinking the last the easiest to do, I submitted this query:
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.
Rich
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2012-01-03 23:38:42 | Re: Single Table Select With Aggregate Function |
| Previous Message | Hagen Finley | 2012-01-03 17:58:51 | Re: Adding German Character Set to PostgresSQL |