select * from trips where trip_id in
(
select substring_index(group_concat(t.trip_id order by NumStops desc), ',', 1) as tripID
from trips t join
     (select st.trip_id, count(*) as NumStops
      from stoptimes st
      group by st.trip_id
     ) st
     on st.trip_id = t.trip_id
group by t.route_id, t.direction_id
)

;