0

I have two tables that look roughly like this:

Airports
uniqueID | Name
0001     | Dallas


Runways
uniqueID | AirportID | Length
000101   | 0001      | 8000

I'm doing a join that looks like this:

SELECT Airports.Name, Runways.Length FROM Airports, Runways 
WHERE Airports.uniqueID==Runways.AirportID

Obviously, each runway has exactly one airport, and each airport has 1..n runways.

For an airport with multiple runways, this gives me several rows, one for each runway at that airport. I want a result set that contains ONLY the row for the longest runway, i.e. MAX(Length). Sometimes, the Length is 0 for several runways in the database, because the source data is missing. In that case I only want one row with the Length = 0 obviously.

I've tried the approach laid out here: Inner Join table with respect to a maximum value but that's actually not helpful because that's like searching for the longest runway of all, not for the longest at one particular airport.

Community
  • 1
  • 1
Philipp
  • 957
  • 1
  • 6
  • 20
  • What criteria is used to determine the runway to return when several have length 0? (the airportID smallest airportID?) – xQbert Jun 10 '14 at 19:05
  • @xQbert It can be random. If they are stored in the DB with Length=0, I don't care. Just pick one. – Philipp Jun 10 '14 at 19:07

1 Answers1

1

This seems to simple to be what you want but it seems to meet all the cases you've described...

SELECT A.Name, Max(R.Length)
FROM Airports A
INNER JOIN Runways R
 on A.uniqueID=R.AirportID
Group by A.Name

This should give you the max runway for each airport.

If you need additional data elements then use the above as a inline view (Subquery within the joins) to limit the results sets to just those airports and their max runway.

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Nope, that's it. If it seems "too simple" then that's because I haven't done any SQL since 2005 so I have forgotten even the simplest things. – Philipp Jun 10 '14 at 19:14