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.