NTSB investigations

20,559 cases where the phase of flight was Initial climb.

Cases, newest first

Query with SQL
duckdb -csv -c "WITH phase_names(main, name) AS (VALUES (100, 'Prior to flight'), (150, 'Standing'), (200, 'Pushback/towing'), (250, 'Taxi'), (300, 'Takeoff'), (350, 'Initial climb'), (400, 'Enroute'), (450, 'Maneuvering'), (500, 'Approach'), (550, 'Landing'), (600, 'Emergency descent'), (650, 'Uncontrolled descent'), (700, 'Post-impact'), (750, 'After landing'), (800, 'Other'), (990, 'Unknown')),
    crosswalk(code, main, name) AS (VALUES (500, 150, 'Standing'), (501, 150, 'Standing - pre-flight'), (502, 150, 'Standing - starting engine(s)'), (503, 150, 'Standing - engine(s) operating'), (504, 150, 'Standing - engine(s) not operating'), (505, 150, 'Standing - idling rotors'), (510, 250, 'Taxi'), (511, 200, 'Taxi - pushback/tow'), (512, 250, 'Taxi - to takeoff'), (513, 250, 'Taxi - from landing'), (514, 250, 'Taxi - aerial'), (520, 300, 'Takeoff'), (521, 300, 'Takeoff - roll/run'), (522, 350, 'Takeoff - initial climb'), (523, 300, 'Takeoff - aborted'), (530, 400, 'Climb'), (531, 400, 'Climb - to cruise'), (540, 400, 'Cruise'), (541, 400, 'Cruise - normal'), (542, 400, 'Maneuvering - holding (IFR)'), (550, 400, 'Descent'), (551, 400, 'Descent - normal'), (552, 600, 'Descent - emergency'), (553, 650, 'Descent - uncontrolled'), (560, 500, 'Approach'), (561, 500, 'Approach - VFR pattern - downwind'), (562, 500, 'Approach - VFR pattern - base turn'), (563, 500, 'Approach - VFR pattern - base leg/base to final'), (564, 500, 'Approach - VFR pattern - final approach'), (565, 500, 'Go-around (VFR)'), (566, 500, 'Approach - IAF to FAF/outer marker (IFR)'), (567, 500, 'Approach - FAF/outer marker to threshold (IFR)'), (568, 500, 'Circling (IFR)'), (569, 500, 'Missed approach (IFR)'), (570, 550, 'Landing'), (571, 550, 'Landing - flare/touchdown'), (572, 550, 'Landing - roll'), (573, 550, 'Landing - aborted'), (574, 550, 'Emergency landing'), (575, 550, 'Emergency landing after takeoff'), (576, 600, 'Emergency descent/landing'), (580, 450, 'Maneuvering'), (581, 450, 'Maneuvering - aerial application'), (582, 450, 'Maneuvering - turn to reverse direction'), (583, 450, 'Maneuvering - turn to landing area (emergency)'), (590, 450, 'Hover'), (591, 450, 'Hover - in ground effect'), (592, 450, 'Hover - out of ground effect'), (600, 800, 'Other'), (610, 990, 'Unknown')),
    current_codes AS (
      SELECT e.case_mkey, TRY_CAST(e.\"tier1Number\" AS INTEGER) AS code,
        row_number() OVER (PARTITION BY e.case_mkey ORDER BY coalesce(e.\"isDefiningEvent\", false) DESC, e.position) AS n
      FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' e
      WHERE e.aircrafts_position = 1 AND e.\"tier1Number\" GLOB '[1-9][0-9]*'
        AND (e.\"isDefiningEvent\" OR e.case_mkey IN (SELECT \"mKey\" FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' WHERE \"isPre1982\"))
    ),
    phases AS (
      SELECT case_mkey, 'current' AS scheme, code, CASE WHEN code >= 990 THEN 990 ELSE code // 50 * 50 END AS main
      FROM current_codes WHERE n = 1
      UNION ALL
      SELECT o.case_mkey, 'older', o.\"phaseOfFlightCode\", w.main
      FROM 'https://openairdocs.org/data/latest/ntsb_occurrences.parquet' o JOIN crosswalk w ON w.code = o.\"phaseOfFlightCode\"
      WHERE o.aircrafts_position = 1 AND o.\"occurrenceNumber\" = 1 AND o.case_mkey NOT IN (SELECT case_mkey FROM current_codes)
    ),
    case_phases AS (
      SELECT p.*, c.\"eventDate\", c.\"highestInjuryLevel\" = 'Fatal' AS fatal, a.aircraft_model_code
      FROM phases p JOIN 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c ON c.\"mKey\" = p.case_mkey
      LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = p.case_mkey AND a.position = 1
      WHERE c.\"eventDate\" >= DATE '1962-01-01' AND p.main IN (SELECT main FROM phase_names)
    )
  SELECT c.\"ntsbNumber\", c.\"eventDate\", c.\"eventType\", c.\"eventCity\", c.\"eventStateOrRegion\", a.n_number, a.\"aircraftMake\", a.\"aircraftModel\", c.\"highestInjuryLevel\", c.\"totalFatal\", c.\"completionStatus\", c.report_url
  FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c.\"mKey\" AND a.position = 1
  WHERE c.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 350)
  ORDER BY c.\"eventDate\" DESC, c.\"ntsbNumber\" DESC"

Prints the whole list as CSV. It reads the release’s Parquet files over HTTPS, so there’s nothing to download first. Install DuckDB to run it.

NTSB ANC66A0025Accident · No injuriesClosedCessna 180 · Bethel, AKDec 20, 1965 · N2714X · Substantial damageNTSB ANC66A0024Accident · No injuriesClosedGrumman G-73 · Bethel, AKDec 20, 1965 · N1208 · Substantial damageNTSB UNK66X0117Accident · No injuriesClosedBeech D18S · Ixcalak, MexicoDec 19, 1965 · N45T · DestroyedNTSB NYC66F0429Accident · No injuriesClosedPiper PA-28 · Narrowsburg, NYDec 19, 1965 · N7981W · Substantial damageNTSB LAX66D0199Accident · No injuriesClosedCessna 140 · San Diego, CADec 19, 1965 · N76403 · Substantial damageNTSB FTW66F0460Accident · Serious injuriesClosedCessna 172A · College Sta, TXDec 19, 1965 · N7190T · Substantial damageNTSB NYC66F0430Accident · Minor injuriesClosedPiper JC3-65 · Pepperell, MADec 18, 1965 · N33121 · Substantial damageNTSB DEN66A0049Accident · Minor injuriesClosedHiller UH12L4 · Albuquerque, NMDec 18, 1965 · N9780C · DestroyedNTSB CHI66D0472Accident · No injuriesClosedLuscombe 8A · Waldo, OHDec 17, 1965 · N71180 · Substantial damageNTSB NYC66D0453Accident · Minor injuriesClosedErcoupe 415-C · Londonderry Tp, PADec 16, 1965 · N2205H · DestroyedNTSB MKC66D0341Accident · No injuriesClosedCessna 150 · St Charles, MODec 16, 1965 · N8666S · Substantial damageNTSB SEA66F0182Accident · Minor injuriesClosedTaylorcraft DC0-65 · Kent, WADec 11, 1965 · N49174 · Substantial damageNTSB FTW66A0055Accident · No injuriesClosedDouglas A-26 · San Antonio, TXDec 6, 1965 · N507WB · Substantial damageNTSB CHI66F0482Accident · No injuriesClosedPiper PA-28 · Lombard, ILDec 6, 1965 · N6774W · Substantial damageNTSB OAK66A0026Accident · 1 fatalClosedMidget-Mstg MM-1 · Hollister, CADec 5, 1965 · N18G · DestroyedNTSB MIA66F0387Accident · 1 fatalClosedAeronca 7AC · Claxton, GADec 5, 1965 · N81509 · DestroyedNTSB MIA66F0337Accident · Minor injuriesClosedLuscombe 8A · Orlando, FLNov 27, 1965 · N1539B · Substantial damageNTSB OAK66F0147Accident · No injuriesClosedPiper PA-28 · Exeter, CANov 26, 1965 · N6884W · Substantial damageNTSB CHI66F0505Accident · Minor injuriesClosedTaylorcraft DCO-65 · Waupaca, WINov 26, 1965 · N53514 · Substantial damageNTSB OAK66F0153Accident · No injuriesClosedCessna 170 · Independence, CANov 25, 1965 · N4135V · Substantial damageNTSB FTW66F0400Accident · No injuriesClosedBeechcraft D35 · Shreveport, LANov 25, 1965 · N2162D · Substantial damageNTSB MKC66F0226Accident · Minor injuriesClosedRyan PT-22 · Valley City, NDNov 21, 1965 · N46751 · Substantial damageNTSB FTW66F0398Accident · No injuriesClosedPiper PA-28 · Elpaso, TXNov 20, 1965 · N6860W · Substantial damageNTSB MIA66F0311Accident · No injuriesClosedBeechcraft G-35 · Orlando, FLNov 16, 1965 · N4444D · Substantial damageNTSB NYC66F0371Accident · No injuriesClosedPiper PA-32 · Tupper Lake, NYNov 15, 1965 · N3323W · Substantial damageNTSB NYC66A0055Accident · 1 fatalClosedCessna 150 · Fairfield, NJNov 15, 1965 · N3576J · DestroyedNTSB MKC66F0303Accident · Minor injuriesClosedCessna 175 · Mullen, NENov 14, 1965 · N7280M · DestroyedNTSB SEA66D0162Accident · No injuriesClosedCessna 140A · Seattle, WANov 13, 1965 · N358TA · Substantial damageNTSB MKC66F0318Accident · Minor injuriesClosedPiper PA-22 · Salina, KSNov 12, 1965 · N1669A · Substantial damageNTSB LAX66F0181Accident · No injuriesClosedPiper PA-22 · 29 Palms, CANov 11, 1965 · N1772P · Substantial damageNTSB CHI66F0401Accident · Serious injuriesClosedLuscombe 8A · Salem, MINov 11, 1965 · N28484 · Substantial damageNTSB ANC66F0078Accident · Serious injuriesClosedPiper PA-18 · Wasilla, AKNov 11, 1965 · N7472D · DestroyedNTSB FTW66F0395Accident · No injuriesClosedBeech 35-33 · Tyler, TXNov 10, 1965 · N634V · Substantial damageNTSB LAX66F0165Accident · Serious injuriesClosedGreat Lakes 2T-1A · Tehachapi, CANov 9, 1965 · N11324 · DestroyedNTSB SEA66F0180Accident · Minor injuriesClosedPiper PA-18A · Huntley, MTNov 8, 1965 · N3688A · Substantial damageNTSB FTW66F0394Accident · No injuriesClosedAero Cmdr 560 · Addison, TXNov 8, 1965 · N312S · Substantial damageNTSB NYC66F0326Accident · No injuriesClosedStearman Hamond · Honesdale, PANov 7, 1965 · N15525 · Substantial damageNTSB FTW66F0391Accident · Serious injuriesClosedStinson V-77 · Lake Dallas, TXNov 7, 1965 · N69565 · DestroyedNTSB CHI66F0422Accident · No injuriesClosedPiper J-3 · Laurium, MINov 7, 1965 · N40854 · Substantial damageNTSB DEN66D0152Accident · No injuriesClosedCessna 150 · Carlsbad, NMNov 6, 1965 · N8931S · Substantial damageNTSB OAK66F0169Accident · No injuriesClosedPiper PA-28 · Honolulu, HINov 5, 1965 · N5794W · Substantial damageNTSB SEA66F0151Accident · No injuriesClosedBeechcraft D-17S · Tacoma, WAOct 31, 1965 · N9000C · Substantial damageNTSB MIA66F0288Accident · Minor injuriesClosedPiper PA-11 · Clanton, ALOct 31, 1965 · N4727H · Substantial damageNTSB FTW66F0405Accident · Serious injuriesClosedPiper PA-22 · Jennings, LAOct 29, 1965 · N6912B · DestroyedNTSB DEN66D0167Accident · No injuriesClosedCessna 182F · Casa Grande, AZOct 29, 1965 · N3536Y · Substantial damageNTSB MIA66F0280Accident · Minor injuriesClosedCessna 140 · Shelby, NCOct 27, 1965 · N76477 · Substantial damageNTSB SEA66F0185Accident · Serious injuriesClosedStinson HW-75 · Port Orchard, WAOct 26, 1965 · N22511 · Substantial damageNTSB MKC66F0284Accident · No injuriesClosedPiper PA-12 · Paxton, NEOct 26, 1965 · N78552 · Substantial damageNTSB MIA66A0046Accident · 1 fatalClosedPiper PA-18 · Gaffney, SCOct 25, 1965 · N8794D · DestroyedNTSB SEA66F0174Accident · No injuriesClosedBeechcraft E35 · Adel, OROct 24, 1965 · N7328B · Destroyed