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 MIA65F0061Accident · Serious injuriesClosedAeronca 7AC · Bay St.Louis, MSApr 17, 1965 · N82527 · Substantial damageNTSB DEN65D0061Accident · No injuriesClosedPiper PA-28 · Yuma, AZApr 16, 1965 · N6349W · Substantial damageNTSB CHI65F0118Accident · Minor injuriesClosedMaule M-4 · Jackson, MIApr 16, 1965 · N4614T · Substantial damageNTSB NYC65F0110Accident · No injuriesClosedLuscombe 8E · Pluckemin, NJApr 15, 1965 · N2646K · Substantial damageNTSB DEN65A0066Accident · No injuriesClosedHiller UH12E · Ne Bouse, AZApr 15, 1965 · N9784C · Substantial damageNTSB MKC65D0097Accident · No injuriesClosedCessna 140 · Paxton, NEApr 14, 1965 · N3739V · Substantial damageNTSB CHI65F0106Accident · Minor injuriesClosedRyan ST-3KR · Dayton, OHApr 14, 1965 · N61747 · Substantial damageNTSB FTW65F0022Accident · Minor injuriesClosedPiper PA-23 · San Antonio, TXApr 13, 1965 · N1429P · DestroyedNTSB NYC65F0182Accident · No injuriesClosedBeechcraft B-33 · Pennington, NJApr 11, 1965 · N9559Y · Substantial damageNTSB MIA65A0100Accident · 2 fatalClosedRyan PT-22 · Jacksonville, FLApr 11, 1965 · N47623 · DestroyedNTSB LAX65D0068Accident · No injuriesClosedMooney M20C · Fullerton, CAApr 11, 1965 · N6278U · Substantial damageNTSB FTW65D0019Accident · No injuriesClosedGlobe GC-1B · Kingsville, TXApr 11, 1965 · N78276 · Substantial damageNTSB CHI65F0153Accident · Serious injuriesClosedPiper PA28 · Columbia, ILApr 11, 1965 · N6489W · DestroyedNTSB MIA65F0052Accident · Serious injuriesClosedAeronca 65TC · Hazlehurst, GAApr 10, 1965 · N31345 · DestroyedNTSB CHI65F0193Accident · Minor injuriesClosedPiper PA-28 · Antioch, ILApr 10, 1965 · N7225W · Substantial damageNTSB CHI65D0077Accident · No injuriesClosedCessna 172 · Cleveland, OHApr 9, 1965 · N7861U · DestroyedNTSB SEA65F0045Accident · Serious injuriesClosedPiper J-3 · Meadows, IDApr 7, 1965 · N69081 · Substantial damageNTSB NYC65F0098Accident · No injuriesClosedFairchild 24R-40 · Winterport, MEApr 4, 1965 · N232E · DestroyedNTSB MKC65A0057Incident · Minor injuriesClosedBannick T · Harvard, NEApr 4, 1965 · N4687T · DestroyedNTSB MKC65F0083Accident · No injuriesClosedHelio H-250 · Pittsburg, KSApr 3, 1965 · N5444E · Substantial damageNTSB FTW66F0069Accident · Minor injuriesClosedBoeing D-75N1 · Snow Lake, ARApr 2, 1965 · N53544 · Substantial damageNTSB SEA65F0035Accident · Minor injuriesClosedCessna 175 · Omak, WAApr 1, 1965 · N9412B · DestroyedNTSB MKC65A0056Accident · Serious injuriesClosedBell 47G3B · Edina, MNApr 1, 1965 · N73222 · Substantial damageNTSB ANC65D0004Accident · No injuriesClosedCessna 180 · Tuntatuliag, AKApr 1, 1965 · N9150C · Substantial damageNTSB OAK65F0052Accident · No injuriesClosedPiper PA-24 · Chico, CAMar 31, 1965 · N7987P · Substantial damageNTSB DEN65A0062Accident · 1 fatalClosedPiper PA-28 · Salt Lake City, UTMar 31, 1965 · N5748W · Substantial damageNTSB LAX65F0038Accident · Minor injuriesClosedLuscombe 8A · Quartz Hill, CAMar 30, 1965 · N71183 · Substantial damageNTSB DEN65A0061Accident · 2 fatalClosedBeechcraft 35 · Aspen, COMar 30, 1965 · N3153V · DestroyedNTSB OAK65F0055Accident · Serious injuriesClosedPiper J5C · Honolulu, HIMar 28, 1965 · N62031 · Substantial damageNTSB MIA65F0023Accident · No injuriesClosedPiper PA-22 · Dunn, NCMar 28, 1965 · N3814P · Substantial damageNTSB LAX65F0045Accident · Serious injuriesClosedPiper PA-28 · Acton, CAMar 28, 1965 · N6260W · DestroyedNTSB DEN65D0037Accident · No injuriesClosedCessna 310D · Aspen, COMar 28, 1965 · N6793T · Substantial damageNTSB SEA65F0025Accident · No injuriesClosedCessna 140 · Lebanon, ORMar 27, 1965 · N2593N · Substantial damageNTSB NYC65F0125Accident · No injuriesClosedCessna 172F · Ambler, PAMar 27, 1965 · N8432U · Substantial damageNTSB CHI65F0105Accident · No injuriesClosedGlobe GC-1B · New Carlisle, OHMar 27, 1965 · N80968 · Substantial damageNTSB OAK65F0020Accident · Serious injuriesClosedBoeing D-75 · Columbia, CAMar 21, 1965 · N105H · DestroyedNTSB MKC65F0056Accident · No injuriesClosedMooney 20C · Beloit, KSMar 17, 1965 · N78874 · Substantial damageNTSB LAX65F0072Accident · Minor injuriesClosedPiper PA-23 · Fullerton, CAMar 16, 1965 · N4307Y · Substantial damageNTSB CHI65F0092Accident · No injuriesClosedMooney M20-D · Roselle, ILMar 16, 1965 · N6637U · Substantial damageNTSB MKC65F0061Accident · Minor injuriesClosedBeechcraft N-35 · Maywood, MNMar 14, 1965 · N637B · DestroyedNTSB DEN65F0024Accident · Minor injuriesClosedCessna 170A · Huntington, UTMar 14, 1965 · N9086A · Substantial damageNTSB NYC65F0080Accident · No injuriesClosedCessna 150 · Hanover, NJMar 13, 1965 · N3511J · Substantial damageNTSB MKC66F0012Accident · Serious injuriesClosedBeechcraft B-35 · Kansas City, MOMar 13, 1965 · N5143C · Substantial damageNTSB NYC65D0180Accident · No injuriesClosedCessna 172 · Parsons, WVMar 12, 1965 · N5550T · Substantial damageNTSB MKC65D0071Accident · No injuriesClosedCessna 150 · Holcomb, MOMar 11, 1965 · N4271U · Substantial damageNTSB CHI65D0121Accident · No injuriesClosedBeechcraft A-55 · Rochester, INMar 11, 1965 · N9706Y · Substantial damageNTSB SEA65F0018Accident · No injuriesClosedCallair A-9 · Genesee, IDMar 9, 1965 · N8268H · Substantial damageNTSB OAK65F0023Accident · No injuriesClosedGlobe GC-1B · Colusa, CAMar 3, 1965 · N3245K · Substantial damageNTSB SEA65D0026Accident · No injuriesClosedCessna 150 · Mitchel, ORMar 2, 1965 · N4320U · Substantial damageNTSB MIA65A0089Accident · 1 fatalClosedPiper J3C-65 · Lumberton, MSMar 2, 1965 · N65901 · Substantial damage