List:General Discussion« Previous MessageNext Message »
From:Jim Faucette Date:July 23 1999 2:44pm
Subject:Re: Query help request
View as plain text  
Wayne Topa wrote:
> 
> I am a newcomer to MYsql/SQL and have a query problem.
> 
> I am collecting weather data into a table
> 
> mysql> describe Weather_Data;
> +-----------+--------------+------+-----+---------------------+-------+
> | Field     | Type         | Null | Key | Default             | Extra |
> +-----------+--------------+------+-----+---------------------+-------+
> | Date_Time | datetime     |      |     | 0000-00-00 00:00:00 |       |
> | Temp_F    | decimal(5,1) | YES  |     | 0.0                 |       |
> | Wind_Dir  | varchar(12)  |      |     |                     |       |
> | Wind_Spd  | decimal(5,1) |      |     | 0.0                 |       |
> +-----------+--------------+------+-----+---------------------+-------+
> 4 rows in set (0.53 sec)
> 
> I am trying to collect information to supply to local TV Weather stations
> so would like to pull from the table
> Date & time Of the High temp, and low temp for the day and also the
> date & time, Direction and speed of the Highest wind gust.
> 
> Seclect's to get any one MIN or MAX Temp work fine but all attempts to
> get the data & time of the MIX/MAX temps/wind speed fail.
> 
> I have tried GROUP BY  and GROUP BY ... index 1 but they fail as well.
> 
> mysql> select * from Weather_Data;
> +---------------------+--------+------------+----------+
> | Date_Time           | Temp_F | Wind_Dir   | Wind_Spd |
> +---------------------+--------+------------+----------+
> | 1999-07-21 13:26:40 |   70.4 | SW 247 Deg |      6.1 |
> | 1999-07-21 13:26:07 |   70.4 | SW 202 Deg |      6.1 |
> | 1999-07-21 13:24:18 |   75.4 | SW         |      6.1 |
> | 1999-07-21 13:24:06 |   76.4 | SW         |      6.4 |
> | 1999-07-21 21:12:05 |   80.4 | SW 247 Deg |      6.1 |
> +---------------------+--------+------------+----------+
> 5 rows in set (0.02 sec)
> 
> mysql> SELECT MIN(Temp_F), Date_Time From Weather_Data group by Temp_F
> limit 1;
> +-------------+---------------------+
> | MIN(Temp_F) | Date_Time           |
> +-------------+---------------------+
> |        70.4 | 1999-07-21 13:26:40 |
> +-------------+---------------------+
> 1 row in set (0.01 sec)
> 
> mysql> SELECT MAX(Temp_F), Date_Time From Weather_Data group by Temp_F
> limit 1;
> +-------------+---------------------+
> | MAX(Temp_F) | Date_Time           |
> +-------------+---------------------+
> |        70.4 | 1999-07-21 13:26:40 |
> +-------------+---------------------+
> 1 row in set (0.00 sec)
> 
> mysql> select MAX(Wind_Spd) from Weather_Data where Date_Time =
> "1999-07-21%";
> +---------------+
> | MAX(Wind_Spd) |
> +---------------+
> |          NULL |
> +---------------+
> 1 row in set (0.00 sec)
> 

When using % you must use LIKE rather than =, so:
SELECT MAX(Wind_Spd) FROM Weather_Data WHERE Date_Time LIKE
"1999-07-21%";

However, you should consider using ORDER BY rather than GROUP BY for
these queries. Using GROUP BY when you want to return more than group
functions can give you unintended results. So I would use these rather
than the ones you're using:

Low Temp:
SELECT Temp_F, Date_Time FROM Weather_Data 
ORDER BY by Temp_F, Date_Time LIMIT 1;

High Temp:
SELECT Temp_F, Date_Time FROM Weather_Data 
ORDER BY by Temp_F DESC, Date_Time LIMIT 1;

  jim...
Thread
Query help requestWayne Topa23 Jul
  • Moving data to another partitionMarkus Senoner23 Jul
    • Re: Moving data to another partitionVivek Khera23 Jul
    • Moving data to another partitionsinisa23 Jul
  • Re: Query help requestJim Faucette23 Jul
    • Re: Query help requestWayne Topa24 Jul
  • Re: Query help requestFaisal Nasim31 Aug