sql - Alternative to mysql WHERE IN SELECT GROUP BY when wanting max value in group by -

sql - Alternative to mysql WHERE IN SELECT GROUP BY when wanting max value in group by -

i have next query, developed hint found online because of problem group by returning maximum value; it's running slowly.

having looked online i'm seeing where in (select.... grouping by) issue, but, honest, i'm struggling find way around this:

select * tbl_berths bring together tbl_active_trains b on a.train_uid=b.train_uid (a.train_id, a.timestamp) in ( select a.train_id, max(a.timestamp) grouping a.train_id )

i'm thinking perchance need derived table, experience in area 0 , it's not working out!

you can move subquery , select required columns instead of (*)

select a.train_uid tbl_berths bring together tbl_active_trains b on a.train_uid=b.train_uid bring together (select a.train_id, max(a.timestamp) timestamp grouping a.train_id )t on a.train_id = t.train_id , a.timestamp = t.timestamp

mysql sql max group greatest-n-per-group