Nested query to find details in table B for maximum value in table A

Question

I've got a huge bunch of flights travelling between airports.

Each airport has an ID and (x,y) coordinates.

For a given list of flights, I want to find the northernmost (highest x) airport visited.

---

Here's the query I'm currently using:

SELECT name,iata,icao,apid,x,y 
  FROM airports 
 WHERE y=(SELECT MAX(y) 
            FROM airports AS a
               , flights AS f 
           WHERE (f.src_apid=a.apid OR f.dst_apid=a.apid) AND [condition]
         )

This works beautifully and reasonably fast as long as y is unique, but fails once it isn't. What I'd want to do instead is find the MAX(y) in the subquery, but return the unique apid for the airport with the highest y. Any suggestions?

Answer 1

third attempt, using assumed user (userid,name) table

select u.name, ap.name
     , ap.iata
     , ap.icao
     , ap.apid
     , ap.x
     , max(ap.y)  
  from users u
     , airports ap
     , flights f
 where u.userid=f.userid
   and (   f.src_apid=ap.apid 
        OR f.dst_apid=ap.apid
       )
group by u.name, ap.name,ap.iata,ap.icao,ap.apid,ap.x 

you can now restrict the query to the one user you are interested in ..

comment on GROUP BY:

• strictly speaking MySQL would allow me to write that group by as 'group by u.name, ap.name'.
• Other SQL dialects don't, they ask that all selected fields that are not aggregated be in the GROUP BY statement.
• So I tend to be 'chicken' when selecting my GROUP BY fields ...