title: MySQL query with GROUP BY behaving differently between MySQL versions?
body: <p>I have two MySQL tables - equipment and calibration, where equipment represents an inventory of equipment and calibration holds records for each equipment calibration. One equipment will have multiple calibrations.</p>
  
  <p>In MySQL 5.5 the following query was fully working to identify equipment where the most recent calibration has expired:</p>
  
  <pre><code>SELECT * FROM equipment AS e
  LEFT JOIN (
    SELECT calibration_id, equipment_id, calibration_company, certificate_no, date_certified, date_nextdue
    FROM (
      SELECT calibration_id, equipment_id, calibration_company, certificate_no, date_certified, date_nextdue
      FROM calibration
      WHERE deleted=0
      ORDER BY date_certified DESC
    ) AS a GROUP BY a.equipment_id
  ) AS c ON c.equipment_id=e.equipment_id
  WHERE e.deleted=0 AND c.date_nextdue &lt;= CURRENT_DATE()
  </code></pre>
  
  <p>However in MySQL 5.7 the same SQL query works but returns rows including the oldest calibration not the most recent.</p>
  
  <p>I've been experimenting with different joins but all need the GROUP BY facility and that seems to be where this all goes wrong.</p>
  
  <p>While the above is a fictional example I have a lot of queries that are very similar in structure and behaving the same way. My question in two parts is:</p>
  
  <ol>
  <li><p>Why is this behaving differently in MySQL 5.7, and </p></li>
  <li><p>What changes do I need to make to the SQL to get it to function as desired in MySQL 5.7?</p></li>
  </ol>
  
  <p>Thank you for your help.</p>