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 UNK64X3077Accident · No injuriesClosedBell 47G · Plymouth, MAJul 27, 1964 · N932B · Substantial damageNTSB UNK64X3076Accident · Minor injuriesClosedHughes 269A · Tennille, GAJul 27, 1964 · N8852F · Substantial damageNTSB UNK64X3057Accident · Serious injuriesClosedBoeing A75N1 · Americus, GAJul 26, 1964 · N73611 · Substantial damageNTSB UNK64X3040Accident · No injuriesClosedFairchild PT26 · Dowington, PAJul 25, 1964 · N37157 · Substantial damageNTSB UNK64X3038Accident · Minor injuriesClosedAeronca 15AC · Spokane, WAJul 25, 1964 · N1326H · Substantial damageNTSB UNK64X3024Accident · Serious injuriesClosedPiper PA22 · Loon Creek, IDJul 25, 1964 · N2676A · DestroyedNTSB UNK64X3014Accident · No injuriesClosedNavion · Elk Grove, CAJul 25, 1964 · N4140K · Substantial damageNTSB UNK64X3003Accident · 2 fatalClosedBeech D-35 · Richland, WAJul 24, 1964 · N4558V · DestroyedNTSB UNK64X3002Accident · 1 fatalClosedCallair A4 · Carson City, MIJul 24, 1964 · N6164C · DestroyedNTSB UNK64X2987Accident · 3 fatalClosedCessna 175A · Arden, NYJul 23, 1964 · N7034E · DestroyedNTSB UNK64X2977Accident · No injuriesClosedAeronca 7AC · Woods Cross, UTJul 22, 1964 · N83647 · Substantial damageNTSB UNK64X2974Accident · No injuriesClosedBoeing YL15 · Anchorage, AKJul 22, 1964 · N4777C · Substantial damageNTSB UNK64X2967Accident · 2 fatalClosedBeechcraft 95B · Winnipeg, CanadaJul 22, 1964 · N9951R · DestroyedNTSB UNK64X2965Accident · No injuriesClosedPiper PA18 · Miami, FLJul 21, 1964 · N7872D · Substantial damageNTSB UNK64X2959Accident · No injuriesClosedPiper PA22 · Hershey, PAJul 21, 1964 · N3222Z · Substantial damageNTSB UNK64X2957Accident · Minor injuriesClosedBoing A75N1 · Damascus, GAJul 21, 1964 · N62288 · DestroyedNTSB UNK64X2939Accident · No injuriesClosedBell 47G3B · Lake City, COJul 20, 1964 · N73915 · Substantial damageNTSB UNK64X2935Accident · No injuriesClosedBell 47D1 · Toppenish, WAJul 19, 1964 · N1161N · Substantial damageNTSB UNK64X2902Accident · No injuriesClosedPiper PA-22 · Madison, INJul 18, 1964 · N7040B · Substantial damageNTSB UNK64X2892Accident · No injuriesClosedBellanca 14132 · Hales Corners, WIJul 18, 1964 · N86806 · Substantial damageNTSB UNK64X2886Accident · 1 fatalClosedPiper PA-22 · Joliet, ILJul 17, 1964 · N9232D · Substantial damageNTSB UNK64X2881Accident · Minor injuriesClosedCessna 175A · Monte Vista, COJul 17, 1964 · N6899E · Substantial damageNTSB UNK64X2864Accident · No injuriesClosedBeechcraft 35A33 · Loveland, COJul 16, 1964 · N9403Y · Substantial damageNTSB UNK64X2863Accident · No injuriesClosedPiper PA22 · Junction City, KSJul 16, 1964 · N8620C · DestroyedNTSB UNK64X2861Accident · No injuriesClosedPiper PA24 · Mott, NDJul 16, 1964 · N7637P · Substantial damageNTSB UNK64X2860Accident · Minor injuriesClosedPiper J4A · Van Wert, OHJul 16, 1964 · N23384 · DestroyedNTSB UNK64X2859Accident · No injuriesClosedTaylorcraft BC12D · Lax Lake, MNJul 16, 1964 · N96646 · Substantial damageNTSB UNK64X2858Accident · No injuriesClosedBeechcraft D35 · Toledo, OHJul 16, 1964 · N2927B · Substantial damageNTSB UNK64X2854Accident · Minor injuriesClosedBoeing E75N1 · Hosston, LAJul 16, 1964 · N5945V · Substantial damageNTSB UNK64X2851Accident · Serious injuriesClosedLockheed L-1049 · New York, NYJul 15, 1964 · N6223C · No damageNTSB UNK64X2848Accident · 3 fatalClosedBoeing 720B · Ansbach, GeorgiaJul 15, 1964 · GE1 · DestroyedNTSB UNK64X2841Accident · No injuriesClosedBoeing A75 · Norton, KSJul 15, 1964 · N61359 · Substantial damageNTSB UNK64X2815Accident · Serious injuriesClosedChampion 7GCB · Raymondville, TXJul 13, 1964 · N9911Y · DestroyedNTSB UNK64X2807Accident · No injuriesClosedPiper PA25 · Fair Oaks, ARJul 13, 1964 · N6778Z · DestroyedNTSB UNK64X2803Accident · No injuriesClosedGrumman G21A · Avalon, CAJul 13, 1964 · N4221A · DestroyedNTSB UNK64X2797Accident · No injuriesClosedCessna 185 · Kotzebue, AKJul 12, 1964 · N9876X · Substantial damageNTSB UNK64X2782Accident · Serious injuriesClosedForney 415C · Purcell, OKJul 12, 1964 · N3244H · DestroyedNTSB UNK64X2780Accident · Serious injuriesClosedPiper PA11 · Sullivan, INJul 12, 1964 · N4767M · DestroyedNTSB UNK64X2755Accident · Minor injuriesClosedLuscombe 8A · Curryville, MOJul 10, 1964 · N1978B · Substantial damageNTSB UNK64X2748Accident · Minor injuriesClosedCessna 140A · St Ansgar, IAJul 10, 1964 · N14310 · Substantial damageNTSB UNK64X2740Accident · Serious injuriesClosedCessna 182 · Roscoe, MTJul 9, 1964 · N9355X · DestroyedNTSB UNK64X2737Accident · No injuriesClosedBeech D35 · Joliet, ILJul 9, 1964 · N3425B · Substantial damageNTSB UNK64X2734Accident · No injuriesClosedBeechcraft B55 · Albuquerque, NMJul 9, 1964 · N2260 · Substantial damageNTSB UNK64X2728Accident · No injuriesClosedBeechcraft M23 · Troutdale, ORJul 9, 1964 · N2382Q · Substantial damageNTSB UNK64X2715Accident · No injuriesClosedBeechcraft M35 · Lakechandler, AKJul 8, 1964 · N9793R · DestroyedNTSB UNK64X2707Accident · 5 fatalClosedLockheed 12A · Martinsville, VAJul 7, 1964 · N18946 · DestroyedNTSB UNK64X2689Accident · No injuriesClosedPiper PA28 · Kansas, ILJul 6, 1964 · N5639W · Substantial damageNTSB UNK64X2680Accident · No injuriesClosedGrumman G164 · Basile, LAJul 6, 1964 · N460RF · Substantial damageNTSB UNK64X2675Accident · Serious injuriesClosedPiper PA20 · Shelby, OHJul 5, 1964 · N7063K · DestroyedNTSB UNK64X2648Accident · 2 fatalClosedCessna 172 · Tulsa, OKJul 4, 1964 · N6496E · Substantial damage