Postgres Pro
Downloads
Home   >   mailing lists

Re: SELECT statement not working as intended - Mailing list pgsql-general

From Henry Drexler
Subject Re: SELECT statement not working as intended
Date
Msg-id CAAtgU9Qo6afDFnYm8XfGj=sqcvSNDoHf2KAdKNALg1rLjdmu0A@mail.gmail.com
Whole thread Raw
In response to SELECT statement not working as intended  (Rich Shepard <rshepard@appl-ecosys.com>)
Responses Re: SELECT statement not working as intended  (Henry Drexler <alonup8tb@gmail.com>)
Re: SELECT statement not working as intended  (Rich Shepard <rshepard@appl-ecosys.com>)
List pgsql-general
Tree view
you are also grouping by sample date, those are the largest values for the criteria you have set out in the group by.



On Mon, Oct 10, 2011 at 6:17 PM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
 I'm trying to query the table to extract the single highest value of a
chemical by location and date. This statement gives me all the values per
stream, site, and date:

SELECT str_name, site_id, sample_date, max(quant) FROM chemistry WHERE hydro
= 'Humboldt' group by str_name, sample_date, site_id order by str_name,
site_id;

 I'm not seeing why this doesn't work; the top few lines of output are:

 CalifCrk    | CalCrk         | 1996-10-18  |    188
 CalifCrk    | CalCrk         | 1996-08-23  |    183
 CalifCrk    | CalCrk         | 1996-07-29  |    201
 CalifCrk    | CalCrk         | 1996-09-27  |    185

when what I want is only the third line.

 A clue to the correct syntax is solicited. I'm sure it's something simple
that I'm just not seeing.

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: SELECT statement not working as intended
Next
From: Henry Drexler
Date:
Subject: Re: SELECT statement not working as intended