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 UNK64X2192Accident · Minor injuriesClosedCessna 150 · Hopkins, MNJun 8, 1964 · N7827Z · DestroyedNTSB UNK64X2191Accident · No injuriesClosedTaylorcraft BC-12D · Anchorage, AKJun 8, 1964 · N29694 · Substantial damageNTSB UNK64X2178Accident · Serious injuriesClosedBeechcraft M23 · Basking Ridge, NJJun 7, 1964 · N2395L · DestroyedNTSB UNK64X2163Accident · No injuriesClosedLuscombe 8A · Lebanon, ILJun 7, 1964 · N1328B · Substantial damageNTSB UNK64X2148Accident · No injuriesClosedCessna 170 · Miami, FLJun 6, 1964 · N4289V · Substantial damageNTSB UNK64X2142Accident · No injuriesClosedAeronca 7AC · Lombard, ILJun 6, 1964 · N82769 · Substantial damageNTSB UNK64X2137Accident · Serious injuriesClosedBeechcraft 9555 · Richlands, VAJun 5, 1964 · N9413Y · DestroyedNTSB UNK64X2114Accident · No injuriesClosedLear M23 · Wichita, KSJun 4, 1964 · N801L · DestroyedNTSB UNK64X2107Accident · No injuriesClosedCessna 170B · Plainville, CTJun 4, 1964 · N3452C · Substantial damageNTSB UNK64X2106Accident · Minor injuriesClosedPiper PA-22 · Gibsonville, NCJun 4, 1964 · N2375A · Substantial damageNTSB UNK64X2092Accident · No injuriesClosedPiper PA18 · Overbrook, KSJun 3, 1964 · N8887D · DestroyedNTSB UNK64X2088Accident · No injuriesClosedCessna 140 · Decatur, INJun 3, 1964 · N76537 · Substantial damageNTSB UNK64X2083Accident · Minor injuriesClosedErcoupe 415C · Woodland, ILJun 3, 1964 · N37123 · DestroyedNTSB UNK64X2082Accident · No injuriesClosedPiper PA18 · Kotzebue, AKJun 3, 1964 · N714 · Substantial damageNTSB UNK64X2075Accident · Minor injuriesClosedCessna 172B · Frazier Park, CAJun 2, 1964 · N7523X · DestroyedNTSB UNK64X2066Accident · No injuriesClosedAngel Specl · Tucson, AZJun 2, 1964 · N6929C · Substantial damageNTSB UNK64X2056Accident · No injuriesClosedBeechcraft D-18S · Memphis, TNMay 31, 1964 · N80492 · DestroyedNTSB UNK64X2050Accident · Minor injuriesClosedErcoupe 415C · Mukwonago, WIMay 31, 1964 · N2043H · DestroyedNTSB UNK64X2032Accident · No injuriesClosedPiper PA18 · Malta, MTMay 30, 1964 · N2534A · Substantial damageNTSB UNK64X2026Accident · Minor injuriesClosedPiper PA22 · St Helen, MIMay 30, 1964 · N3735Z · DestroyedNTSB UNK64X2005Accident · Minor injuriesClosedPiper PA18 · Sturtevant, WIMay 29, 1964 · N7077D · Substantial damageNTSB UNK64X2004Accident · No injuriesClosedPiper J3 · Winifred, MTMay 29, 1964 · N3666K · Substantial damageNTSB UNK64X1995Accident · No injuriesClosedPiper J3 · Sandy, ORMay 28, 1964 · N7074H · Substantial damageNTSB UNK64X1991Accident · No injuriesClosedBeechcraft F35 · Brady, TXMay 28, 1964 · N5016B · Substantial damageNTSB UNK64X1988Accident · No injuriesClosedPiper PA20 · Center, COMay 28, 1964 · N6978K · Substantial damageNTSB UNK64X1981Accident · No injuriesClosedPiper PA22 · Warsaw, INMay 27, 1964 · N3822P · DestroyedNTSB UNK64X1975Accident · No injuriesClosedRB Brooks Spec · Jerseyville, ILMay 27, 1964 · N6326D · Substantial damageNTSB UNK64X1962Accident · No injuriesClosedTemco Swift GC1B · Ansonia, CTMay 26, 1964 · N2350B · Substantial damageNTSB UNK64X1958Accident · 1 fatalClosedAeronca 11AC · Madras, ORMay 26, 1964 · N9181E · DestroyedNTSB UNK64X1946Accident · Serious injuriesClosedPiper PA20 · Greenville, MEMay 24, 1964 · N8043C · DestroyedNTSB UNK64X1927Accident · Minor injuriesClosedPiper PA22 · Rumford, MEMay 24, 1964 · N9511D · Substantial damageNTSB UNK64X1904Accident · No injuriesClosedPiper PA15 · Solon, OHMay 23, 1964 · N4185H · Substantial damageNTSB UNK64X1894Accident · No injuriesClosedTaylorcraft A · Robstown, TXMay 23, 1964 · N19832 · Substantial damageNTSB UNK64X1875Accident · Minor injuriesClosedStinson 108-3 · Isabelle, MNMay 22, 1964 · N6937M · Substantial damageNTSB UNK64X1863Accident · No injuriesClosedPiper PA22 · Middlesex, NYMay 20, 1964 · N2357A · Substantial damageNTSB UNK64X1854Accident · 1 fatalClosedGrumman TBM3U · Willcox, AZMay 19, 1964 · N3968A · DestroyedNTSB UNK64X1821Accident · No injuriesClosedBellanca 1413 · Fairfield, NJMay 18, 1964 · N74403 · Substantial damageNTSB UNK64X1814Accident · Serious injuriesClosedPiper PA22 · The Dalles, ORMay 17, 1964 · N3640P · DestroyedNTSB UNK64X1806Accident · No injuriesClosedAeronca 11CC · Mt Vernon, GAMay 17, 1964 · N4297E · Substantial damageNTSB UNK64X1783Accident · Minor injuriesClosedRyan ST3KR · Angleton, TXMay 16, 1964 · N51122 · DestroyedNTSB UNK64X1780Accident · No injuriesClosedPiper PA22 · Augusta, MEMay 16, 1964 · N5955Z · Substantial damageNTSB UNK64X1753Accident · No injuriesClosedBoeing A75 · Chandler, AZMay 13, 1964 · N48765 · Substantial damageNTSB UNK64X1745Accident · 1 fatalClosedPiper PA23 · Seward, AKMay 12, 1964 · N3384P · DestroyedNTSB UNK64X1738Accident · No injuriesClosedPiper PA24 · Beeville, TXMay 12, 1964 · N7324P · Substantial damageNTSB UNK64X1732Accident · No injuriesClosedPiper PA-22 · Clarksville, TNMay 12, 1964 · N5708Z · Substantial damageNTSB UNK64X1729Accident · 1 fatalClosedCessna 150 · Gastonia, NCMay 11, 1964 · N7721E · DestroyedNTSB UNK64X1718Accident · No injuriesClosedBellanca 14133 · Costa Mesa, CAMay 11, 1964 · N74476 · Substantial damageNTSB UNK64X1717Accident · No injuriesClosedCallair A5T · Bay City, TXMay 11, 1964 · N2948G · DestroyedNTSB UNK64X1703Accident · Minor injuriesClosedPiper PA-28 · Marysville, KSMay 10, 1964 · N8755W · Substantial damageNTSB UNK64X1699Accident · Minor injuriesClosedCessna 140 · Glenwood, ILMay 10, 1964 · N89671 · Substantial damage