SELECT statement not working as intended

On Mon, Oct 10, 2011 at 5: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.

remove the sample_date the group by and the select list. by having it
in there you are asking for the max for each specific sample date.

merlin

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:

for instance

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

188 is the biggest number for 1996-10-18 calcrk califcrk, and so on down the
line.

You need to eliminate the date column in the query, or whatever fits your
requirements.

On Mon, Oct 10, 2011 at 6:24 PM, Henry Drexler <alonup8tb@gmail.com> wrote:

On Mon, 10 Oct 2011, Merlin Moncure wrote:

remove the sample_date the group by and the select list. by having it in
there you are asking for the max for each specific sample date.

merlin,

That tells me the max quant but not on what date. Do I write a nested
SELECT to get that, too?

Thanks,

Rich

On Mon, 10 Oct 2011, Henry Drexler wrote:

you are also grouping by sample date, those are the largest values for the
criteria you have set out in the group by.

Henry,

As I asked Merlin, what is necessary to get the date that maximum quantity
was recorded? A nested SELECT?

Thanks,

Rich

On 10/10/11 3:45 PM, Rich Shepard wrote:

As I asked Merlin, what is necessary to get the date that maximum
quantity
was recorded? A nested SELECT?

the complication is, there can be more than one date with the same
maximum value, so such a query would be ambiguous, or it would return
multiple rows.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On Mon, 10 Oct 2011, John R Pierce wrote:

the complication is, there can be more than one date with the same maximum
value, so such a query would be ambiguous, or it would return multiple
rows.

John,

The likelihood of that is diminishingly small.

Thanks,

Rich

On Mon, Oct 10, 2011 at 4: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.

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 order by 4 limit 1;

On Mon, Oct 10, 2011 at 6:14 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Mon, Oct 10, 2011 at 4: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.

Correction.

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 order by 4 desc limit 1;

On 10/10/2011 05:52 PM, Rich Shepard wrote:

On Mon, 10 Oct 2011, John R Pierce wrote:

the complication is, there can be more than one date with the same
maximum
value, so such a query would be ambiguous, or it would return multiple
rows.

John,

The likelihood of that is diminishingly small.

Thanks,

Rich

Starting the timer now on when that is retracted :)

rjs