On Feb 5, 12:37 pm, "PWX" <ajhmy...@mac.com> wrote:
> On Feb 4, 3:34 pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>
>
>
> > PWX wrote:
> > > I'm a somewhat educated newbie in advanced MySQL queries.
>
> > > I'm attempting to create a variety of "top 10" lists related to a
> > > database of meteorology information. This database records sensor data
> > > every five minutes, and has been doing so since 2000. For the sake of
> > > this post, let's assume that my database has only two fields: recdate
> > > -- bigint(12) -- and temp -- float(7,3). The recdate format is
> > > YYYYMMDDhhmm.
>
> > > Creating a query to find the maximum temperature since the inception
> > > of the database proved pretty easy:
>
> > > SELECT recdate, temp FROM wxtable WHERE recdate BETWEEN 200001010000
> > > AND 200702042355 ORDER BY temp DESC LIMIT 1
>
> > > Result:
> > > 200608011630 96.8
>
> > > But when creating a TOP 10 list of highest daily temperatures over the
> > > period was more problematic:
>
> > > SELECT temp FROM wxtable WHERE recdate BETWEEN 200001010000 AND
> > > 200702042355 ORDER BY temp DESC LIMIT 10
>
> > > Result:
> > > 200608011630 96.8
> > > 200608011720 96.8
> > > 200608011625 96.6
> > > 200608011635 96.6
> > > 200608011650 96.6
> > > 200608011655 96.6
> > > 200608011700 96.6
> > > 200608011715 96.6
> > > 200607161455 96.6
> > > 200607161450 96.6
>
> > > Because the weather data is recorded every five minutes, most of my
> > > results came from the readings around the time of the all-time max
> > > reading above. So I did get the top 10 readings, but not limited to
> > > one per day over the specified range.
>
> > > My question is:
> > > Based on the database's use of a 12-digit integer for recording the
> > > date, how can I limit my results to display only one maximum
> > > temperature for each 24-hour period and therefore get a true top 10
> > > list of daily temperatures.
>
> > > Thanks...
>
> > Use MAX() and GROUP BY utilising only the date portion of recdate.
>
> Thanks for the suggestion. Here's what I came up with. I got
> unpredictable results with this new query:
>
> SELECT MAX(temp), recdate FROM wxtable GROUP BY SUBSTR(recdate,1,8)
> ORDER BY temp DESC LIMIT 10
>
> Result:
> 96.8 200608010000
> 94.9 200608020000
> 85.9 200608030000
> 91.6 200507250000
> 90.3 200307040000
> 94.3 200506250000
> 87.4 200506280000
> 94.5 200207020000
> 92.9 200607170000
> 86.7 200208020000
>
> I ran this query a second time without LIMIT, and while I did appear
> to get one unique high temp for each day, the sort was haphazard. The
> one at the top is indeed the hottest. But the others in the top 10
> shown here are not the hottest. So I think I'm closing in but
> something about my sort isn't solid. Anything pop out any anyone?

Hate to answer my own post, but I'm still needing some guidance.
Strawberry over in comp.databases told me to use the Strawberry query,
which got me close:

SELECT wx.recdate, wx.temp
FROM wxtable wx,(
SELECT max( temp ) as maxtemp, recdate
FROM wxtable
GROUP BY recdate
) maxresults
WHERE wx.recdate = maxresults.recdate
AND wx.temp = maxresults.maxtemp
ORDER BY temp DESC LIMIT 10

Result:
200608011630 96.8
200608011720 96.8
200607161450 96.6
200607161455 96.6
200608011625 96.6
200608011635 96.6
200608011650 96.6
200608011655 96.6
200608011700 96.6
200608011715 96.6

This is almost identical to my first post. But I got some results and
I have more confidence that these are exactly in order. So, if I try
to use SUBSTR with GROUP BY to find the high temperature that is
unique to each 24-hour period....

SELECT wx.recdate, wx.temp
FROM wxtable wx,(
SELECT max( temp ) as maxtemp, recdate
FROM wxtable
GROUP BY SUBSTR(recdate,1,8)
) maxresults
WHERE wx.recdate = maxresults.recdate
AND wx.temp = maxresults.maxtemp
ORDER BY temp DESC LIMIT 10

The unintended result is that I get the highest temperatures at
MIDNIGHT (0000) in the database:

200608030000 85.9
200207090000 82.2
200607020000 82
200509140000 78.8
200407140000 77.7
200204190000 76.7
200408280000 76.3
200208230000 75.1
200407230000 74.9
200608270000 74.7

Ideas?