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 UNK64X1176Accident · No injuriesClosedBeechcraft 95 · Freeland, MIApr 5, 1964 · N2763Y · Substantial damageNTSB UNK64X1175Accident · No injuriesClosedCessna 210 · Webber Falls, OKApr 5, 1964 · N7471E · Substantial damageNTSB UNK64X1174Accident · Serious injuriesClosedPiper PA24 · Alexandria, LAApr 4, 1964 · N7752P · DestroyedNTSB UNK64X1172Accident · 2 fatalClosedAeronca 15AC · Freswate Bay, AKApr 4, 1964 · N1248H · DestroyedNTSB UNK64X1153Accident · Minor injuriesClosedCarson C4 · Boston, MAApr 3, 1964 · N612X · Substantial damageNTSB UNK64X1134Accident · No injuriesClosedStinson 108 · Orville, CAApr 2, 1964 · N8555K · Substantial damageNTSB UNK64X1132Accident · Minor injuriesClosedSikorsky S-55 · Sarawak, MalaysiaApr 2, 1964 · N405A · DestroyedNTSB UNK64X1130Accident · 2 fatalClosedBeechcraft BE-50 · Harrison, ARApr 1, 1964 · N151WB · DestroyedNTSB UNK64X1121Accident · 1 fatalClosedMooney M-18C · Roanoke Rapids, NCMar 31, 1964 · N345M · DestroyedNTSB UNK64X1114Accident · No injuriesClosedPiper PA-23 · Detroit, MIMar 31, 1964 · N1435P · Substantial damageNTSB UNK64X1105Accident · Serious injuriesClosedPiper PA22 · Union, SCMar 29, 1964 · N8824C · Substantial damageNTSB UNK64X1104Accident · Serious injuriesClosedAeronca 7AC · San Diego, CAMar 29, 1964 · N82082 · DestroyedNTSB UNK64X1103Accident · Serious injuriesClosedPiper PA22 · Bowling Green, MOMar 29, 1964 · N3533P · DestroyedNTSB UNK64X1100Accident · 2 fatalClosedLuscombe 8E · Houma, LAMar 29, 1964 · N2636K · DestroyedNTSB UNK64X1097Accident · Minor injuriesClosedPiper PA22 · Cecil, GAMar 29, 1964 · N962A · Substantial damageNTSB UNK64X1090Accident · No injuriesClosedPiper PA20 · Mt View, OKMar 29, 1964 · N7446K · Substantial damageNTSB UNK64X1087Accident · 2 fatalClosedNavion D · Vera Cruz, MexicoMar 29, 1964 · N8086E · DestroyedNTSB UNK64X1082Accident · Minor injuriesClosedPiper PA-18 · Enterprise, ALMar 28, 1964 · N10556 · DestroyedNTSB UNK64X1072Accident · No injuriesClosedCessna 172 · Elizabethville, PAMar 28, 1964 · N8750B · Substantial damageNTSB UNK64X1067Accident · Minor injuriesClosedPiper PA16 · Henryetta, OKMar 28, 1964 · N5956H · Substantial damageNTSB UNK64X1064Accident · Serious injuriesClosedBell 47G2A · Leeville, LAMar 27, 1964 · N6747D · DestroyedNTSB UNK64X1049Accident · No injuriesClosedBeechcraft 23 · Madison, MOMar 27, 1964 · N2323J · Substantial damageNTSB UNK64X1035Accident · No injuriesClosedPiper PA23 · Reno, NVMar 25, 1964 · N1211P · DestroyedNTSB UNK64X1031Accident · No injuriesClosedHiller UH12B · Van Nuys, CAMar 24, 1964 · N3112G · Substantial damageNTSB UNK64X1026Accident · 1 fatalClosedCessna 140 · Westboro, MAMar 23, 1964 · N76660 · DestroyedNTSB UNK64X1020Accident · No injuriesClosedDehavilland DHC-2 · Umiat, AKMar 22, 1964 · N1416Z · Substantial damageNTSB UNK64X1013Accident · No injuriesClosedPiper PA12 · Carthage, TNMar 22, 1964 · N91748 · Substantial damageNTSB UNK64X1006Accident · Minor injuriesClosedAeronca 7AC · Cuero, TXMar 21, 1964 · N2062E · DestroyedNTSB UNK64X1004Accident · No injuriesClosedCessna 172B · Gallup, NMMar 21, 1964 · N7668X · Substantial damageNTSB UNK64X0980Accident · Serious injuriesClosedBellanca 14-13 · Santa Ana, CAMar 19, 1964 · N86893 · DestroyedNTSB UNK64X0979Accident · Serious injuriesClosedBoeing PT17 · Pullman, WAMar 19, 1964 · N58700 · Substantial damageNTSB UNK64X0898Accident · No injuriesClosedBeechcraft 35 · Savannah, GAMar 13, 1964 · N2953V · DestroyedNTSB UNK64X0895Accident · No injuriesClosedPiper PA22 · Wauchula, FLMar 12, 1964 · N7596D · Substantial damageNTSB UNK64X0891Accident · No injuriesClosedMooney M21 · Kansas City, KSMar 12, 1964 · N6456U · Substantial damageNTSB UNK64X0873Accident · Minor injuriesClosedPiper PA22 · Condon, ORMar 10, 1964 · N2938P · DestroyedNTSB UNK64X0868Accident · No injuriesClosedCessna 172 · Uncertain, TXMar 10, 1964 · N7058A · Substantial damageNTSB UNK64X0853Accident · 1 fatalClosedPiper J-3C · Chunchula, ALMar 8, 1964 · N24879 · DestroyedNTSB UNK64X0846Accident · No injuriesClosedPiper PA18 · Vinalhaven, MEMar 8, 1964 · N3809Z · Substantial damageNTSB UNK64X0845Accident · No injuriesClosedBeechcraft 23 · Woodstown, NJMar 8, 1964 · N2339L · Substantial damageNTSB UNK64X0808Accident · No injuriesClosedPiper J3C · Lancaster, MOMar 5, 1964 · N3643K · Substantial damageNTSB UNK64X0796Accident · No injuriesClosedMooney M21 · Modesto, CAMar 3, 1964 · N1324W · Substantial damageNTSB UNK64X0792Accident · No injuriesClosedGrummam G164 · Boca Raton, FLMar 2, 1964 · N491Y · DestroyedNTSB UNK64X0784Accident · No injuriesClosedPiper PA22 · New Kingston, PAMar 1, 1964 · N9243D · Substantial damageNTSB UNK64X0776Accident · No injuriesClosedPiper PA18 · Intl Falls, MNMar 1, 1964 · N165T · Substantial damageNTSB UNK64X0775Accident · No injuriesClosedErcoupe 415C · Harwington, CTMar 1, 1964 · N93584 · Substantial damageNTSB UNK64X0764Accident · 1 fatalClosedPiper PA22 · Birmingham, MIFeb 29, 1964 · N5608P · DestroyedNTSB UNK64X0743Accident · Minor injuriesClosedBeechcraft F35 · Johnstown, PAFeb 29, 1964 · N506 · Substantial damageNTSB UNK64X0711Accident · No injuriesClosedBeechcraft 35 · Blue Ash, OHFeb 27, 1964 · N2779V · Substantial damageNTSB UNK64X0684Accident · No injuriesClosedMooney M20A · Covington, GAFeb 24, 1964 · N8390E · Substantial damageNTSB UNK64X0682Accident · No injuriesClosedBoeing B-720B · Miam, FLFeb 24, 1964 · N726US · Substantial damage