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 OAK67A0041Accident · 3 fatalClosedCessna 172F · Woodford, CAOct 2, 1966 · N5655R · DestroyedNTSB LAX67D0155Accident · Minor injuriesClosedCessna 172 · Sonoita, AZOct 2, 1966 · N5932R · DestroyedNTSB LAX67A0035Accident · 4 fatalClosedBeech A35 · Seattle, WAOct 2, 1966 · N728B · DestroyedNTSB CHI67A0045Accident · 3 fatalClosedPiper PA-22 · Knox, INOct 2, 1966 · N3135Z · DestroyedNTSB CAN66F0421Accident · No injuriesClosedCessna 170B · Tabusintac Rv., CanadaOct 2, 1966 · N3133A · Substantial damageNTSB OAK67F0150Accident · Serious injuriesClosedPiper PA-28 · Meyers, CAOct 1, 1966 · N5925W · DestroyedNTSB OAK67A0040Accident · 4 fatalClosedBeech 35 · Salinas, CAOct 1, 1966 · N55F · DestroyedNTSB LAX67A0034Accident · Minor injuriesClosedBell 47G3B1 · Pearlblosom, CAOct 1, 1966 · N1166W · Substantial damageNTSB FTW67F0241Accident · No injuriesClosedCessna 210 · Fort Worth, TXOct 1, 1966 · N7369E · Substantial damageNTSB MKC67F0315Accident · Minor injuriesClosedWaco RNF · Chesterfield, MOSep 29, 1966 · N144Y · Substantial damageNTSB MIA67F0305Accident · Serious injuriesClosedPiper PA-28 · Gainesville, FLSep 29, 1966 · N9087W · Substantial damageNTSB FTW67F0307Accident · No injuriesClosedCessna 180 · Bayou Sorrel, LASep 29, 1966 · N3237D · DestroyedNTSB DEN67D0149Accident · No injuriesClosedCessna 150 · Artesia, COSep 28, 1966 · N5747E · Substantial damageNTSB FTW67F0239Accident · Minor injuriesClosedCallair A-9 · El Paso, TXSep 27, 1966 · N9619Z · DestroyedNTSB MIA67A0037Accident · 4 fatalClosedCessna 170A · N.Wilkesboro, NCSep 25, 1966 · N5744C · DestroyedNTSB DEN67D0137Accident · No injuriesClosedBeech A23 · Lake George, COSep 25, 1966 · N5780V · Substantial damageNTSB UNK66X0178Accident · No injuriesClosedMooney M20E · Toronto Is, CanadaSep 24, 1966 · N6990U · Substantial damageNTSB MIA67D0242Accident · Minor injuriesClosedPiper J-3 · Florence, ALSep 24, 1966 · N98069 · Substantial damageNTSB FTW67D0220Accident · No injuriesClosedBeech 23 · Augusta, ARSep 24, 1966 · N5983S · Substantial damageNTSB MKC67F0269Accident · No injuriesClosedBeech H35 · Waterloo, IASep 23, 1966 · N5429D · Substantial damageNTSB MKC67F0228Accident · Minor injuriesClosedChampion 7ECA · Stanley, KSSep 23, 1966 · N8394V · Substantial damageNTSB LAX67D0143Accident · No injuriesClosedPiper PA-28 · Mt.Trumble, AZSep 23, 1966 · N8608W · Substantial damageNTSB FTW67A0038Accident · No injuriesClosedBell 47J · Leeville, LASep 23, 1966 · N6736D · Substantial damageNTSB SEA67F0189Accident · Serious injuriesClosedCessna T-210F · Hillsboro, ORSep 21, 1966 · N6731R · DestroyedNTSB SEA67A0030Accident · Minor injuriesClosedBensen B8-M · Albany, ORSep 20, 1966 · N74115 · DestroyedNTSB OAK67A0035Accident · 1 fatalClosedAero Comdr 560 · Las Vegas, NVSep 18, 1966 · N280E · Substantial damageNTSB FTW67F0253Accident · No injuriesClosedMooney M-18C · Monroe, LASep 18, 1966 · N4051 · Substantial damageNTSB NYC67A0047Accident · No injuriesClosedLockheed 14N · Medford, NJSep 17, 1966 · N18138 · Substantial damageNTSB LAX67A0033Accident · 1 fatalClosedN.American AJ-1 · N.Hollywood, CASep 17, 1966 · N9143Z · DestroyedNTSB SEA67F0217Accident · No injuriesClosedCessna 170A · Chiloquin, ORSep 16, 1966 · N5471C · Substantial damageNTSB OAK67F0086Accident · No injuriesClosedCessna 182G · Sacramento, CASep 16, 1966 · N3439S · Substantial damageNTSB NYC67A0049Accident · 1 fatalClosedHughes 269A · Lincoln, RISep 16, 1966 · N8799F · DestroyedNTSB ANC67D0081Accident · No injuriesClosedCessna 150F · Paxson, AKSep 16, 1966 · N8078S · Substantial damageNTSB CHI67F0264Accident · No injuriesClosedErcoupe 415-C · Plymouth, MISep 15, 1966 · N28945 · Substantial damageNTSB ANC67D0045Accident · No injuriesClosedPiper PA-18 · Big Delta, AKSep 15, 1966 · N7384D · Substantial damageNTSB MKC67D0278Accident · No injuriesClosedPiper PA-25 · Wardell, MOSep 14, 1966 · N6679Z · Substantial damageNTSB DEN67A0019Accident · 1 fatalClosedPiper PA-32 · Granby, COSep 12, 1966 · N3387W · DestroyedNTSB CHI67D0182Accident · No injuriesClosedCessna 310J · Bloomington, ILSep 12, 1966 · N309FS · Substantial damageNTSB MKC67F0217Accident · No injuriesClosedAeronca 65-LA · Rose Hill, KSSep 11, 1966 · N24240 · Substantial damageNTSB MKC67F0201Accident · No injuriesClosedPiper J-3 · Bagley, MNSep 11, 1966 · N98502 · Substantial damageNTSB DEN67F0097Accident · Serious injuriesClosedCessna 150 · Uffalo, WYSep 11, 1966 · N3919U · DestroyedNTSB CHI67F0190Accident · No injuriesClosedTaylorcraft DCO-65 · Cottage Grove, WISep 11, 1966 · N50702 · Substantial damageNTSB CHI67F0159Accident · Minor injuriesClosedPiper PA-22 · Brighton, MISep 11, 1966 · N1239C · Substantial damageNTSB SEA67F0129Accident · Minor injuriesClosedPiper PA-12 · Thomp. Falls, MTSep 10, 1966 · N78576 · Substantial damageNTSB NYC67D0253Accident · No injuriesClosedCessna 172 · Stratford, CTSep 10, 1966 · N8445U · Substantial damageNTSB MKC67F0202Accident · No injuriesClosedErcoupe 415-C · Blaisdell, NDSep 10, 1966 · N28953 · Substantial damageNTSB MIA67F0261Accident · Minor injuriesClosedAeronca 65-LB · St-Stephens, SCSep 10, 1966 · N33701 · DestroyedNTSB SEA67F0111Accident · No injuriesClosedCessna 182 · Dupuyer, MTSep 9, 1966 · N3175S · Substantial damageNTSB OAK67A0032Accident · No injuriesClosedBell 47G3B1 · Big Pine, CASep 9, 1966 · N73958 · Substantial damageNTSB NYC67F0237Accident · No injuriesClosedStinson 108-1 · Clinton, MDSep 9, 1966 · N97749 · Substantial damage