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 UNK64X0681Accident · 2 fatalClosedPiper J3 · Greenville, MSFeb 23, 1964 · N2069M · DestroyedNTSB UNK64X0669Accident · Serious injuriesClosedMooney M20A · Columbus, OHFeb 23, 1964 · N8149E · Substantial damageNTSB UNK64X0651Accident · No injuriesClosedCessna 172 · Imperial, CAFeb 22, 1964 · N5633A · Substantial damageNTSB UNK64X0648Accident · No injuriesClosedBeechcraft 35 · Idaho Falls, IDFeb 22, 1964 · N5579D · Substantial damageNTSB UNK64X0644Accident · No injuriesClosedBeechcraft 35 · Crystal, MNFeb 22, 1964 · N4261D · Substantial damageNTSB UNK64X0641Accident · Serious injuriesClosedChampion 7AC · Kansas City, MOFeb 22, 1964 · N84554 · Substantial damageNTSB UNK64X0636Accident · No injuriesClosedPiper PA24 · Guymon, OKFeb 21, 1964 · N7335P · Substantial damageNTSB UNK64X0621Accident · Minor injuriesClosedCessna 310G · Raleigh, NCFeb 20, 1964 · N2929R · DestroyedNTSB UNK64X0617Accident · 2 fatalClosedCessna 180 · Isla Mujeres, MexicoFeb 20, 1964 · N5272D · DestroyedNTSB UNK64X0594Accident · Minor injuriesClosedMeyers 200B · Cleveland, OHFeb 17, 1964 · N229RS · Substantial damageNTSB MIA64I0081Incident · No injuriesClosedDouglas DC-8B · San Juan, PRFeb 17, 1964 · N8607 · Minor damageNTSB UNK64X0588Accident · No injuriesClosedCessna 150 · Broomfield, COFeb 16, 1964 · N7025X · Substantial damageNTSB UNK64X0581Accident · Serious injuriesClosedPiper PA-23 · Cairo, EgyptFeb 16, 1964 · N2274P · DestroyedNTSB NYC65I0107Incident · No injuriesClosedBoeing 707 · Lisbon, POFeb 15, 1964 · N764TW · Minor damageNTSB UNK64X0556Accident · Minor injuriesClosedNavy N3N · Chandler, AZFeb 13, 1964 · N44824 · Substantial damageNTSB UNK64X0548Accident · No injuriesClosedStinson V77 · Diomede Is, AKFeb 12, 1964 · N52904 · Substantial damageNTSB UNK64X0532Accident · 1 fatalClosedCessna 140A · Shafter, CAFeb 10, 1964 · N9686A · DestroyedNTSB UNK64X0505Accident · No injuriesClosedHughes 269A · Opa Locka, FLFeb 9, 1964 · N8735F · Substantial damageNTSB UNK64X0491Accident · No injuriesClosedCessna 150 · Park Hall, MDFeb 8, 1964 · N7009X · DestroyedNTSB UNK64X0471Accident · No injuriesClosedCessna 170A · Albion, MIFeb 5, 1964 · N9260A · DestroyedNTSB UNK64X0464Accident · No injuriesClosedCessna 170B · Hamilton, OHFeb 5, 1964 · N78CP · DestroyedNTSB UNK64X0459Accident · No injuriesClosedPiper J3C · Taylor, NEFeb 4, 1964 · N42453 · Substantial damageNTSB UNK64X0452Accident · No injuriesClosedLuscombe 8A · Poughkeepsie, NYFeb 3, 1964 · N2367K · Substantial damageNTSB UNK64X0448Accident · 2 fatalClosedChampion 7GCB · Lithonia, GAFeb 3, 1964 · N9972Y · DestroyedNTSB UNK64X0447Accident · 10 fatalClosedBeechcraft D18S · Gainesville, FLFeb 3, 1964 · N2999 · DestroyedNTSB UNK64X0434Accident · Minor injuriesClosedTaylorcraft BC12D1 · Defiance, OHFeb 2, 1964 · N5179M · Substantial damageNTSB UNK64X0420Accident · No injuriesClosedCessna 140 · Rotan Tex, TXFeb 1, 1964 · N71300 · Substantial damageNTSB UNK64X0418Accident · Minor injuriesClosedPiper PA-22 · St Charles Co, MOFeb 1, 1964 · N5961D · Substantial damageNTSB UNK64X0408Accident · No injuriesClosedBeechcraft AT-11 · Sint Maarten Bwi, NTJan 31, 1964 · N8045H · Substantial damageNTSB UNK64X0392Accident · Serious injuriesClosedPiper PA22 · Yuma, AZJan 28, 1964 · N1396C · DestroyedNTSB UNK64X0378Accident · No injuriesClosedBell 47G2 · Hendersonville, NCJan 27, 1964 · N5194B · Substantial damageNTSB UNK64X0366Accident · No injuriesClosedAeronca 65 · Milwaukee, WIJan 26, 1964 · N29416 · Substantial damageNTSB UNK64X0356Accident · No injuriesClosedSwift GC1B · Olathe, KSJan 25, 1964 · N3843K · Substantial damageNTSB UNK64X0341Accident · No injuriesClosedPiper PA-23 · Santa Maria, CAJan 24, 1964 · N3380P · Substantial damageNTSB UNK64X0329Accident · No injuriesClosedLuscombe 8A · East Hanover, NJJan 23, 1964 · N2719K · DestroyedNTSB UNK64X0288Accident · No injuriesClosedPiper J3 · Bridgewater, VAJan 19, 1964 · N51570 · Substantial damageNTSB UNK64X0266Accident · No injuriesClosedLuscombe 8A · Woodbury, TNJan 17, 1964 · N1080K · Substantial damageNTSB UNK64X0238Accident · No injuriesClosedCessna 310G · Wayne, MIJan 13, 1964 · N8977Z · Substantial damageNTSB UNK64X0217Accident · No injuriesClosedCessna 150 · Fresno, CAJan 11, 1964 · N7729E · Substantial damageNTSB UNK64X0198Accident · Minor injuriesClosedPiper PA25 · Winters, CAJan 9, 1964 · N6487Z · Substantial damageNTSB UNK64X0195Accident · 2 fatalClosedDehavilland 1046BA · Stuart, FLJan 9, 1964 · N4267C · DestroyedNTSB UNK64X0194Accident · 1 fatalClosedBeechcraft J35 · Hill City Kans, KSJan 9, 1964 · N634T · DestroyedNTSB UNK64X0173Accident · No injuriesClosedPiper PA22 · Toledo, OHJan 5, 1964 · N8545C · Substantial damageNTSB UNK64X0170Accident · Minor injuriesClosedPiper J4F · Kalamazoo, MIJan 5, 1964 · N30431 · Substantial damageNTSB UNK64X0169Accident · No injuriesClosedPiper J3 · Altoona, FLJan 5, 1964 · N98337 · Substantial damageNTSB UNK64X0161Accident · No injuriesClosedStinson 108-1 · Chesapeake, VAJan 5, 1964 · N97861 · Substantial damageNTSB UNK64X0159Accident · Serious injuriesClosedPiper PA-24 · Hartsville, SCJan 5, 1964 · N5121P · Substantial damageNTSB UNK64X0155Accident · No injuriesClosedPiper PA24 · Wheeling, ILJan 4, 1964 · N6526P · Substantial damageNTSB UNK64X0133Accident · No injuriesClosedPiper PA22 · Worcester, MAJan 3, 1964 · N3632Z · Substantial damageNTSB UNK64X0119Accident · Minor injuriesClosedLuscombe 8F · Excelsior, MNJan 2, 1964 · N1828B · Substantial damage