Nested query to find details in table B for maximum value in table A
- by jpatokal
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)
)
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?
