Finding the MAX value for each day in a month over multiple years in MySQL
<p>I've got a database full of weather data...specifically the date, max temp, min temp, and daily rainfall for more than 100 years.  I'm trying to find the maximum temperature for each day and the specific date that it occurred over the entire 100+ years.</p>

<p>My table is set up similar to below...</p>

<pre><code>+-------+------------+------+------+------+
| id    | date       | thi  | tlo  | rain |
+-------+------------+------+------+------+
| 42856 | 2016-01-01 |   49 |   39 | 0.00 |
| 42857 | 2016-01-02 |   51 |   38 | 0.00 |
| 42858 | 2016-01-03 |   60 |   37 | 0.00 |
| 42859 | 2016-01-04 |   54 |   32 | 0.00 |
| 42860 | 2016-01-05 |   47 |   32 | 0.00 |
+-------+------------+------+------+------+
5 rows in set (0.01 sec)
</code></pre>

<p>I want to find the max(thi) for each day of the year and the date in which it occurred.  This data goes back to 1899 so there are 117 January's in the database and so on for each year.</p>

<p>I have come up with the following so far...</p>

<pre><code>select date, max(thi),
-&gt; DAY(date)
-&gt; from dfw where MONTH(date)='01'
-&gt; group by DAY(date);
+------------+----------+-----------+
| date       | max(thi) | DAY(date) |
+------------+----------+-----------+
| 1899-01-01 |       83 |         1 |
| 1899-01-02 |       78 |         2 |
| 1899-01-03 |       84 |         3 |
| 1899-01-04 |       81 |         4 |
| 1899-01-05 |       82 |         5 |
| 1899-01-06 |       79 |         6 |
| 1899-01-07 |       83 |         7 |
| 1899-01-08 |       88 |         8 |
| 1899-01-09 |       82 |         9 |
| 1899-01-10 |       79 |        10 |
| 1899-01-11 |       83 |        11 |
| 1899-01-12 |       82 |        12 |
| 1899-01-13 |       78 |        13 |
| 1899-01-14 |       79 |        14 |
| 1899-01-15 |       80 |        15 |
| 1899-01-16 |       81 |        16 |
| 1899-01-17 |       79 |        17 |
| 1899-01-18 |       80 |        18 |
| 1899-01-19 |       84 |        19 |
| 1899-01-20 |       83 |        20 |
| 1899-01-21 |       79 |        21 |
| 1899-01-22 |       85 |        22 |
| 1899-01-23 |       88 |        23 |
| 1899-01-24 |       82 |        24 |
| 1899-01-25 |       84 |        25 |
| 1899-01-26 |       82 |        26 |
| 1899-01-27 |       81 |        27 |
| 1899-01-28 |       85 |        28 |
| 1899-01-29 |       84 |        29 |
| 1899-01-30 |       86 |        30 |
| 1899-01-31 |       93 |        31 |
+------------+----------+-----------+
31 rows in set (0.01 sec)
</code></pre>

<p>This gives me the maximum for each day in January which is good...but I need the date on which it occurred.  For some reason all I am getting is 1899.</p>

<p>For example on January 31...the max(thi) is 93 but it occurred on 1911-01-31.  There are also times in which the max(thi) could have occurred in multiple years.  On January 30...the max(thi) is 86 which occurred on 1906-01-30 and 1994-01-30.</p>

<p>Is there a way to do this in MySQL or am I just out of luck?  Thanks in advance!</p>