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 SEA69A0055Accident · 2 fatalClosedPiper PA-16 · La Grande, ORMay 16, 1969 · N6853K · DestroyedNTSB NYC69D0459Accident · Minor injuriesClosedLake LA-4 · Copake, NYMay 16, 1969 · N2036L · DestroyedNTSB DEN69F0241Accident · No injuriesClosedDouglas A-26C · Silver City, NMMay 16, 1969 · N9301R · Substantial damageNTSB OAK69D0314Accident · No injuriesClosedCessna 188 · Exeter, CAMay 14, 1969 · N8098V · DestroyedNTSB LAX69A0066Accident · 3 fatalClosedPiper PA-24 · Tucson, AZMay 12, 1969 · N7740P · DestroyedNTSB OAK70F0002Accident · No injuriesClosedBeech A35 · Mina, NVMay 11, 1969 · N792B · Substantial damageNTSB FTW69D0528Accident · No injuriesClosedPiper PA-28 · Greenville, TXMay 11, 1969 · N7510J · Substantial damageNTSB DIA69D0183Accident · No injuriesClosedCessna 172 · Rutherfordton, NCMay 11, 1969 · N7465A · Substantial damageNTSB MKC69F0420Accident · No injuriesClosedPiper PA-28 · Kanapolis, KSMay 10, 1969 · N8048N · Substantial damageNTSB LAX70F0043Accident · 4 fatalClosedDouglas B-26B · Los Angeles, CAMay 10, 1969 · N9405Z · DestroyedNTSB LAX69F0365Accident · Minor injuriesClosedTaylor JK-1 · Chino, CAMay 10, 1969 · N8011 · Substantial damageNTSB FTW69F0517Accident · No injuriesClosedPiper PA-28 · Haskell, OKMay 10, 1969 · N5762F · Substantial damageNTSB OAK69F0302Accident · No injuriesClosedPiper PA-25 · Stockton, CAMay 9, 1969 · N4429Y · Substantial damageNTSB MKC69D0437Accident · No injuriesClosedCessna 182F · Wamego, KSMay 9, 1969 · N3562U · Substantial damageNTSB SEA69F0244Accident · No injuriesClosedGrumman TBM-3E · Ironside, ORMay 8, 1969 · N9564Z · Substantial damageNTSB OAK69D0305Accident · No injuriesClosedCallair A-9B · Pleasnt Grove, CAMay 8, 1969 · N7613V · Substantial damageNTSB FTW69F0492Accident · No injuriesClosedPiper PA-22 · Keene, TXMay 8, 1969 · N3720P · Substantial damageNTSB DIA69I0062Incident · No injuriesClosedDouglas DC-9 · Washington, DCMay 8, 1969 · N8924E · No damageNTSB DEN70D0013Accident · No injuriesClosedAeronca 7AC · Sedalia, COMay 8, 1969 · N84902 · Substantial damageNTSB SEA70D0028Accident · No injuriesClosedBeech B23 · Nehalem, ORMay 7, 1969 · N2361J · Substantial damageNTSB NYC69F0452Accident · No injuriesClosedAero Comdr 560 · Flushing, NYMay 6, 1969 · N371 · Substantial damageNTSB MKC69F0457Accident · No injuriesClosedMooney M-20C · Lake Andes, SDMay 5, 1969 · N7820V · Substantial damageNTSB MKC69F0446Accident · No injuriesClosedCallair A-9 · Kadoka, SDMay 5, 1969 · N9653Z · Substantial damageNTSB CHI70F0004Accident · No injuriesClosedCallair A-9 · Cayce, KYMay 5, 1969 · N3583G · Substantial damageNTSB ANC69A0052Accident · Minor injuriesClosedHiller UH-12C · Livengood, AKMay 5, 1969 · N5330V · Substantial damageNTSB NYC69F0460Incident · No injuriesClosedAero Comdr 560E · Morristown, NJMay 4, 1969 · N6895S · Minor damageNTSB NYC69F0411Accident · Serious injuriesClosedCessna 172D · Darragh, PAMay 4, 1969 · N2350U · DestroyedNTSB MKC70F0003Accident · No injuriesClosedAero Comdr 200-D · Columbus, NEMay 4, 1969 · N2971T · Substantial damageNTSB LAX70F0002Accident · No injuriesClosedCessna 140 · Agua Dulce, CAMay 4, 1969 · N2190V · Substantial damageNTSB FTW69D0503Accident · Serious injuriesClosedHiggins H-3 · Chattanooga, OKMay 4, 1969 · N61H · DestroyedNTSB CHI69F0609Accident · Minor injuriesClosedCessna 150 · Cincinnati, OHMay 4, 1969 · N6620T · DestroyedNTSB SEA69D0232Accident · No injuriesClosedMaule M4-210 · Dubois, IDMay 3, 1969 · N4621T · Substantial damageNTSB FTW69A0079Accident · 4 fatalClosedM-Saulnier MS-760 · Evadale, TXMay 3, 1969 · N760M · DestroyedNTSB CHI69D0633Accident · No injuriesClosedCessna 177 · Austin, INMay 2, 1969 · N2929X · Substantial damageNTSB MKC70F0069Accident · Serious injuriesClosedCessna 150 · Grain Valley, MOMay 1, 1969 · N2281J · DestroyedNTSB NYC69F0437Accident · No injuriesClosedCessna 185E · Mattituck, NYApr 30, 1969 · N2294T · Substantial damageNTSB FTW69D0511Accident · No injuriesClosedPiper PA-28 · Eldorado, TXApr 30, 1969 · N95160 · Substantial damageNTSB SEA69F0253Accident · 1 fatalClosedPiper PA-24 · Plains, MTApr 29, 1969 · N7311P · DestroyedNTSB FTW69F0465Accident · No injuriesClosedGrumman G-164 · Lake Charles, LAApr 29, 1969 · N10275 · Substantial damageNTSB MIA69F0489Accident · No injuriesClosedPiper PA-24 · Atlanta, GAApr 28, 1969 · N5117P · Substantial damageNTSB OAK70D0006Accident · No injuriesClosedGrumman G-164 · Nelson, CAApr 27, 1969 · N616Y · Substantial damageNTSB NYC69A0098Accident · No injuriesClosedCessna 150F · Mechanicsburg, PAApr 27, 1969 · N8063F · Substantial damageNTSB MIA69D0449Accident · No injuriesClosedPiper PA-28 · Centre, ALApr 27, 1969 · N9829W · Substantial damageNTSB LAX69F0339Accident · Serious injuriesClosedPiper PA-28 · El Cajon, CAApr 27, 1969 · N6427J · Substantial damageNTSB FTW69F0490Accident · Minor injuriesClosedBoeing E75 · Winnie, TXApr 27, 1969 · N4590N · Substantial damageNTSB SEA69D0231Accident · No injuriesClosedPiper PA-28R · Friday Harbr, WAApr 26, 1969 · N9448N · Substantial damageNTSB OAK69F0297Accident · Serious injuriesClosedAeronca KC · Five Points, CAApr 26, 1969 · N17485 · DestroyedNTSB DEN69F0228Accident · Minor injuriesClosedPiper PA-28 · Albuquerque, NMApr 26, 1969 · N6626W · Substantial damageNTSB CHI70F0007Accident · Minor injuriesClosedCallair A-9 · Bronson, MIApr 26, 1969 · N3603G · Substantial damageNTSB CHI69F0636Accident · Minor injuriesClosedPiper PA-28 · E Palestine, OHApr 26, 1969 · N8389R · Destroyed