title: MYSQL - Selecting the most recent date for each record (correlated subqueries)
body: <p>I have a mysql query where I am trying to get:</p>
  
  <ul>
  <li>the most recent service of a vehicle using date (obtained from repair table) (anything that is not 'None' from serviceType means that it is a service)</li>
  <li>and the most recent vehicle counter using date (obtained from vehicle fueling table)</li>
  </ul>
  
  <p>In a single record while having one vehicle ID for each record. The data is obtained from 2 different tables while grouping them by vehicle ID in sub-queries. </p>
  
  <pre><code>SELECT 
  r.vehicleCounter AS lastServiceCounter, 
  vf.vehicleCounter AS currentCounter,
  r.dateFinished,
  r.type,
  v.vehicleID
  FROM vehicle AS v
  LEFT JOIN (
          SELECT r.repairID,
          r.vehicleCounter,
          r.dateFinished,
          st.type,
          r.vehicleID
          FROM repair AS r
          INNER JOIN servicetype AS st 
          ON st.serviceTypeID = r.serviceTypeID
  
          WHERE st.type &lt;&gt; 'None' 
          AND r.dateFinished IN (
              SELECT MAX(dateFinished)
              FROM repair
              GROUP BY vehicleID
          )
     ) AS r  
     ON r.vehicleID = v.vehicleID
  LEFT JOIN (
      SELECT vehicleCounter, vehicleID
      FROM vehiclefueling
      WHERE date IN(
          SELECT MAX(date)
          FROM vehicleFueling
          GROUP BY vehicleID
      )
  ) AS vf ON vf.vehicleID = v.vehicleID 
  </code></pre>
  
  <p>While running this query I should have only a record for each ID but at the moment the query is returning the same ID with different values for a record (check ID 11):</p>
  
  <p><a href="https://i.stack.imgur.com/bpIzr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bpIzr.png" alt="returned query"></a></p>
  
  <p>I could use GROUP BY vehicleID for the main query but I want to keep the most recent value</p>