Window functions can help here. You can try a self-join with MySQL (and it won't do a hash join because it doesn't have it; it didn't have hash joins at all bef

Window functions can help here. You can try a self-join with MySQL (and it won't do a hash join because it doesn't have it; it didn't have hash joins at all before 8.10 and it still doesn't have hash outer joins, and what you want to eliminate the older rows is an anti-join, i.e. asserting null on a left join) but honestly you're better off sorting by timestamp and using a manual window when processing the results.