MYSQL - Selecting the most recent date for each record (correlated subqueries)

Question

I have a mysql query where I am trying to get:

• 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)
• and the most recent vehicle counter using date (obtained from vehicle fueling table)

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.

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 <> '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 

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):

I could use GROUP BY vehicleID for the main query but I want to keep the most recent value

Answer 1

I would get the repair and fueling ids using correlated subqueries:

select v.*,
       (select r.repairId
        from repairs r
        where r.vehicleID = v.vehicleID
        order by r.datefinished desc
        limit 1
       ) as repairid,
       (select vh.vehiclefuelingId
        from vehiclefueling vh
        where vh.vehicleID = v.vehicleID
        order by vh.date desc
        limit 1
       ) as vehiclefuelingId
from vehicle v;

Then, you can join back to the two tables to get additional fields.

This should give you some idea on how to approach the problem. Your sample query has additional tables and logic that don't fit directly into your question.