Thread: SELECT statement not working as intended

   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

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

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 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 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 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