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 UNK64X3563Accident · Minor injuriesClosedPiper PA28 · Pittstown, NJAug 23, 1964 · N5237W · Substantial damageNTSB UNK64X3541Accident · Minor injuriesClosedCallair A5 · Camillia, GAAug 22, 1964 · N9970C · DestroyedNTSB UNK64X3540Accident · No injuriesClosedFairchild WM62C · Salome, AZAug 22, 1964 · N57064 · Substantial damageNTSB UNK64X3528Accident · Minor injuriesClosedPiper PA28 · Branson, MOAug 22, 1964 · N5082W · Substantial damageNTSB UNK64X3522Accident · No injuriesClosedPiper PA18A · Pecos, TXAug 21, 1964 · N8892D · DestroyedNTSB UNK64X3487Accident · No injuriesClosedBeechcraft B95A · Gozad, NEAug 20, 1964 · N9695Y · DestroyedNTSB UNK64X3481Accident · Minor injuriesClosedPiper J3 · Talladega, ALAug 19, 1964 · N98023 · DestroyedNTSB UNK64X3480Accident · Minor injuriesClosedCessna 175 · Mcnabb, ILAug 19, 1964 · N6919E · Substantial damageNTSB UNK64X3472Accident · No injuriesClosedCessna 120 · Ann Arbor, MIAug 19, 1964 · N1175V · Substantial damageNTSB UNK64X3464Accident · No injuriesClosedMaule M4 · Hysham, MTAug 18, 1964 · N4705T · Substantial damageNTSB UNK64X3459Accident · Minor injuriesClosedPiper PA12 · Grants, NMAug 18, 1964 · N2795M · Substantial damageNTSB UNK64X3446Accident · No injuriesClosedPiper PA18A · Bunkie, LAAug 18, 1964 · N2892Z · Substantial damageNTSB UNK64X3432Accident · No injuriesClosedNavion B · Thermol, CAAug 16, 1964 · N4235K · DestroyedNTSB UNK64X3423Accident · Minor injuriesClosedCessna 140 · Waukesea, WIAug 16, 1964 · N89345 · DestroyedNTSB UNK64X3407Accident · Serious injuriesClosedRyan ST3KR · Fairfield, NJAug 15, 1964 · N666K · DestroyedNTSB UNK64X3401Accident · Serious injuriesClosedCessna 172 · Crossville, TNAug 15, 1964 · N6687A · DestroyedNTSB UNK64X3396Accident · No injuriesClosedBeech C-45F · Kankakee, ILAug 15, 1964 · N12068 · DestroyedNTSB UNK64X3394Accident · No injuriesClosedPiper PA-28 · Lebanon, NHAug 15, 1964 · N5604W · Substantial damageNTSB UNK64X3387Accident · No injuriesClosedBoeing A75N1 · Thibodaux, LAAug 15, 1964 · N55361 · Substantial damageNTSB UNK64X3383Accident · No injuriesClosedCessna 195 · New York, NYAug 14, 1964 · N9305R · Substantial damageNTSB UNK64X3373Accident · No injuriesClosedBoeing B75N1 · St James, LAAug 14, 1964 · N64546 · Substantial damageNTSB UNK64X3352Accident · No injuriesClosedAeronca 7AC · Clinton, MOAug 13, 1964 · N3880E · Substantial damageNTSB UNK64X3346Accident · No injuriesClosedCallair A5 · Victoria, TXAug 12, 1964 · N2937G · Substantial damageNTSB UNK64X3345Accident · No injuriesClosedPiper PA24 · Fall River, KSAug 12, 1964 · N7797P · Substantial damageNTSB UNK64X3344Accident · Minor injuriesClosedLuscombe 8C · Godley, TXAug 12, 1964 · N37050 · DestroyedNTSB UNK64X3312Accident · Serious injuriesClosedMooney M20C · Blaney Park, MIAug 9, 1964 · N6412U · DestroyedNTSB UNK64X3310Accident · 2 fatalClosedPiper PA-22 · Bluefield, WVAug 9, 1964 · N3236Z · DestroyedNTSB UNK64X3309Accident · Serious injuriesClosedBeechcraft H18S · Cincinnati, OHAug 9, 1964 · N517DC · DestroyedNTSB UNK64X3302Accident · No injuriesClosedLuscombe 8A · Statesville, NCAug 9, 1964 · N71166 · Substantial damageNTSB UNK64X3290Accident · No injuriesClosedBeechcraft 35 · Meade Lake, KSAug 9, 1964 · N3758N · Substantial damageNTSB UNK64X3289Accident · No injuriesClosedBeechcraft B35 · Fresno, CAAug 9, 1964 · N8869A · Substantial damageNTSB UNK64X3250Incident · No injuriesClosedDouglas DC-3 · Point Barrow, AKAug 7, 1964 · N21769 · Minor damageNTSB UNK64X3233Accident · No injuriesClosedPiper PA22 · Mulino, ORAug 6, 1964 · N7102D · Substantial damageNTSB UNK64X3220Accident · No injuriesClosedPiper J3 · Issue, MDAug 5, 1964 · N6993H · Substantial damageNTSB UNK64X3209Accident · No injuriesClosedCessna 170B · Stuckey, SCAug 4, 1964 · N4588C · Substantial damageNTSB UNK64X3201Accident · Serious injuriesClosedFairchild M62C · Mojave, CAAug 3, 1964 · N55921 · DestroyedNTSB UNK64X3198Accident · No injuriesClosedTaylorcraft BC12D · Clare, MIAug 3, 1964 · N39372 · DestroyedNTSB UNK64X3186Accident · 2 fatalClosedPiper PA-22 · Bloomsburg, PAAug 2, 1964 · N8109D · DestroyedNTSB UNK64X3184Accident · Serious injuriesClosedCessna 120 · Danville, INAug 2, 1964 · N1764V · DestroyedNTSB UNK64X3169Accident · No injuriesClosedBeechcraft 35 · Mayer, AZAug 2, 1964 · N3748N · DestroyedNTSB UNK64X3162Accident · 1 fatalClosedPiper J4F · Raleigh, NCAug 1, 1964 · N30802 · DestroyedNTSB UNK64X3159Accident · 1 fatalClosedBoeing A75N1 · San Antonio, TXAug 1, 1964 · N62552 · DestroyedNTSB UNK64X3150Accident · Minor injuriesClosedFairchild M62A3 · Mt.Cobb, PAAug 1, 1964 · N69595 · DestroyedNTSB UNK64X3149Accident · No injuriesClosedLuscombe 8A · Sanford, NCAug 1, 1964 · N45951 · Substantial damageNTSB UNK64X3120Accident · Minor injuriesClosedPiper PA18 · Willcox, AZJul 30, 1964 · N7223C · DestroyedNTSB UNK64X3113Accident · No injuriesClosedBell 47G · Dublin, PAJul 29, 1964 · N616X · Substantial damageNTSB UNK64X3106Accident · No injuriesClosedPiper PA18 · Pluckemin, NJJul 29, 1964 · N8217C · Substantial damageNTSB UNK64X3102Accident · No injuriesClosedPiper PA30 · Hillsboro, ORJul 29, 1964 · N7398Y · Substantial damageNTSB UNK64X3081Accident · No injuriesClosedMooney MK21E · Crystal River, FLJul 28, 1964 · N1302W · Substantial damageNTSB UNK64X3079Accident · 1 fatalClosedNavion NAV-4 · Mount Pocono, PAJul 27, 1964 · N4303K · Destroyed