Ordering SQL results by attributes in another table

Question

If I have a table of Buses that has many Stops, and each Stop record has an arrival time, how do I retrieve and order Buses by the earliest Stop time?

 _______    ________
| Buses |  | Stops  |
|-------|  |--------|
| id    |  | id     |
| name  |  | bus_id |
 -------   | time   |
            --------

I'm able to do it with the following query:

SELECT DISTINCT sub.id, sub.name
FROM
(SELECT buses.*, stops.time
 FROM buses
 INNER JOIN stops ON stops.bus_id = buses.id
 ORDER BY stops.time) AS sub;

...but this has the downsides of having to do 2 queries and having to specify all the fields from buses in the SELECT DISTINCT clause. That gets particularly annoying if the buses table ever changes.

What I want to do is this:

SELECT DISTINCT buses.*
FROM buses
INNER JOIN stops ON stops.bus_id = buses.id
ORDER BY stops.time;

...however in order to get DISTINCT buses.*, I have to include stops.time there as well, which gives me duplicate buses with different stop times.

What would be a better way to do this query?

Answer 1

One thing you can do is to put the inner query into ORDER BY. This will keep the outer query "clean" as it will only select from buses. This way you won't need to return any additional fields.

SELECT buses.*
FROM buses
ORDER BY (
  SELECT MIN(stops.time) FROM stops WHERE stops.bus_id = buses.id
)

Answer 2

specifying fields in select is best practice so i'm not sure why that is listed as downside.

I would do

Select buses.* From buses inner join
(Select stops.bus_id, min(stops.time) as mintime 
    From Stops 
    Group By stops.bus_id) st on buses.id = st.bus_id

or

Select buses.*, min(stops.time) as stoptime 
    From buses inner join stops on 
    buses.ID = stops.bus_ID group by buses.id, buses.name