Need to group by the vehicle id from the online & offline date history logs and get the latest date of history

I have below the tables

1. tbl_vehicles

2. tbl_status_history

Now, I need to group the vid and get their latest status history. For example in vid:1 under tbl_status_history. This vid latest status history is online so the result for vid 1 would be the date_online 2023-03-02.

Below is my target result.

I used the code of @nnichols and updated it to the below code to get also the latest online date but had still not worked out how to sort the two dates(Online & offline) before the group by

SELECT h.id, h.vid, h.accnt_id, h.date_online, h.date_offline, v.platenumber
FROM tbl_status_history h
JOIN (
    SELECT vid, MAX(date_offline) max_date_offline, MAX(date_online) max_date_online
    FROM tbl_status_history
    GROUP BY vid
) h2 ON h.vid = h2.vid AND h.date_offline = h2.max_date_offline and h.date_online = h2.max_date_online
JOIN tbl_vehicles v ON h.vid = v.vid and h.accnt_id = 1;

This is one way to produce your "target result" using ROW_NUMBER():

SELECT h.id, h.vid, h.accnt_id, h.date_online, h.date_offline, v.platenumber
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY vid ORDER BY GREATEST(date_online, date_offline) DESC) rn
    FROM tbl_status_history
    WHERE accnt_id = 1
) h
JOIN tbl_vehicles v ON h.vid = v.vid
WHERE h.rn = 1;

Or, the pre MySQL 8 method:

SELECT h.id, h.vid, h.accnt_id, h.date_online, h.date_offline, v.platenumber
FROM tbl_status_history h
JOIN (
    SELECT vid, GREATEST(MAX(date_offline), MAX(date_online)) max_date
    FROM tbl_status_history
    WHERE accnt_id = 1
    GROUP BY vid
) h2 ON h.vid = h2.vid AND (h.date_offline = h2.max_date OR h.date_online = h2.max_date)
JOIN tbl_vehicles v ON h.vid = v.vid;

You should consider changing the structure of tbl_status_history to:

• accnt_id is in tbl_vehicles, where it belongs, so redundant here
• two DATE columns to store one status change date does not make sense
• you could drop id too, in favour of the natural key, bit I probably wouldn't go that far