title: MySQL finding MAX of a given field and returning the entire row
body: <h1>Introduction</h1>
  
  <p>Hi,<br>
  I have a weather station that records the current weather every half an hour.<br>
  Among other sensors, it has a thermometer. It is the subject in this enquiry.<br>
  I have summarized the fields of my table, but please be aware that <code>HiTemp</code> field stores the maximum temperature.  </p>
  
  <h1>Situation</h1>
  
  <p>Given the following table structure in a SQL query  </p>
  
  <pre><code>CREATE TABLE weather (
      Id INT AUTO_INCREMENT PRIMARY KEY,
      HiTemp FLOAT,
      Date VARCHAR(8),
      Time VARCHAR(5),
      Epoch BIGINT
  );
  </code></pre>
  
  <p><strong>Note that</strong> <code>HiTemp</code> is stored in celsius degrees, <code>Date</code> follows <code>dd/mm/YY</code> format and <code>Time</code> is stored in CET (GMT+1) timezone<br>
  Therefore, a sample record would be..  </p>
  
  <pre><code>1, 19, "13/12/19", "13:00", 1576238400
  </code></pre>
  
  <p>My goal is to sort through the records and find the maximum value of <code>HiTemp</code> along its other fields for every year in the table.<br>
  To find unique years a simple substring on <code>Date</code> works fine  </p>
  
  <pre><code>SELECT SUBSTR(Date, -2) AS Year FROM weather GROUP BY Year;
  </code></pre>
  
  <p>I was thinking to use a <code>PROCEDURE</code> (function) to be able to use some kind of loop to go over the years<br>
  This query right here is the closest to my goal I have been able to figure out on my own  </p>
  
  <pre><code>SELECT MAX(HiTemp) MaxTemp, SUBSTR(Date, -2) Year, Date, Time FROM weather GROUP BY Year;
  </code></pre>
  
  <p>Problem here is that as far as I know <code>MAX</code> is a group function and the other selected fields are irrelevant to its value<br>
  Nevertheless, the output of this query would have a format like the following  </p>
  
  <pre><code>40.1, 19, "28/06/19", "17:00"
  37.5, 18, "04/08/18", "14:00"
  35.5, 17, "05/08/17", "15:30"
  </code></pre>
  
  <p><strong>Note that</strong> these records are correct in the sense that the resulting selected fields belong to the very same record (they were handpicked)  </p>
  
  <p>Any ideas? Subqueries, Joins, Procedures.. Any insight on this is very much appreciated, I'm pretty lost at this point.<br>
  Thanks.</p>