title: Get greatest (or lowest) field in mysql table when join already exists
body: <p>I want to get the greatest (or lowest) value in a field for a specific value of a different field but I am a bit lost. I am already aware of answered questions on the topic, but I already have a join in my query and I can't apply the terrific answers I found on my specific problem.</p>
  
  <p>I have two tables, namely register and records. Records has all (weather) stations listed once for each month (each stationid represented 12 times, if complete data exists, a stationid can thus not be presented more than 12 times), and register has all stations listed with some of their characteristics. For the sake of the example, the two tables look pretty much like this:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `records` (
  `stationid` varchar(30),
  `month` int(11),
  `tmin` decimal(3,1),
  `tmax` decimal(3,1),
  `commentsmax` text,
  `commentsmin` text,
  UNIQUE KEY `webcode` (`stationid`,`month`)
  );
  
  INSERT INTO `records` (`stationid`, `month`, `tmin`, `tmax`, `commentsmin`, `commentsmax`) VALUES
  ('station1', 7, '10.0', '46.0', 'Extremely low temperature.', 'Very high temperature.'),
  ('station2', 7, '15.0', '48.0', 'Very low temperature.', 'Extremely low temperature.'),
  ('station1', 1, '-10', '15', 'Extremely low temperature.', 'Somewhat high temperature.');
  
  CREATE TABLE IF NOT EXISTS `register` (
  `stationid` varchar(30),
  `stationname` varchar(40),
  `stationowner` varchar(10),
  `georegion` varchar(40),
  `altitude` int(4),
  KEY `stationid` (`stationid`)
  );
  
  INSERT INTO `register` (`stationid`, `stationname`, `stationowner`, `georegion`, `altitude`) VALUES
  ('station1', 'Halifax', 'Maria', 'the North', 16),
  ('station2', 'Leeds', 'Peter', 'the South', 240);
  </code></pre>
  
  <p>The desired output is:</p>
  
  <pre><code>+-------------+-------+-------+---------------+-----------+----------+-----------------------------+
  | stationname | month | tmin  |  stationowner | georegion | altitude |        commentsmin          |
  +-------------+-------+-------+---------------+-----------+----------+-----------------------------+
  | Leeds       |     7 |  15.0 | Peter         | the South |      240 |  Very low temperature       |
  | Halifax     |     1 | -10.0 | Maria         | the North |       16 |  Extremely low temperature  |
  +-------------+-------+-------+---------------+-----------+----------+-----------------------------+
  </code></pre>
  
  <p>where each station appears only one with the lowest temperatures from table 'records', including some station properties from the table 'register'. I am using the following code:</p>
  
  <pre><code>SELECT register.stationname, records.month, min(records.tmin), register.stationowner, register.georegion, register.altitude,  records.commentsmin FROM records INNER JOIN register ON records.stationid=register.stationid GROUP BY records.stationid ORDER BY min(tmin) ASC
  </code></pre>
  
  <p>but it doesn't give the correct bits of the records table corresponding to the lowest tmin values BY stationid when there are many records in the tables.</p>
  
  <p>I have seen solutions like this one here: <a href="https://stackoverflow.com/questions/29372841/mysql-greatest-n-results-with-join-tables">MySQL Greatest N Results with Join Tables</a>, but I just can't get my head around applying it on my two tables. I would be grateful for any ideas!</p>