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 MKC70F0287Accident · Minor injuriesClosedPiper PA-22 · Columbia, MOOct 15, 1969 · N3577H · Substantial damageNTSB NYC70F0192Accident · No injuriesClosedPiper PA-28 · Groton, CTOct 14, 1969 · N7214J · Substantial damageNTSB NYC70F0184Accident · Minor injuriesClosedJeane Tenie · Nashua, NHOct 13, 1969 · N8123 · DestroyedNTSB LAX70F0157Accident · 1 fatalClosedAero Comdr S-2R · Shafter, CAOct 13, 1969 · N8988Q · Substantial damageNTSB ANC70A0032Accident · 6 fatalClosedCessna TU206B · Anchorage, AKOct 13, 1969 · N4977F · DestroyedNTSB CHI70F0280Accident · No injuriesClosedPiper PA-28 · West Milton, OHOct 12, 1969 · N4715L · Substantial damageNTSB ANC70D0086Accident · No injuriesClosedPiper PA-14 · Wasilla, AKOct 12, 1969 · N4205H · Substantial damageNTSB NYC70F0193Accident · No injuriesClosedOldfield BGL · Export, PAOct 11, 1969 · N181H · Substantial damageNTSB IAD70A0025Accident · 1 fatalClosedPiper J-3 · Topping, VAOct 11, 1969 · N92437 · Substantial damageNTSB SEA70A0014Accident · 1 fatalClosedCessna 320A · Conner, MTOct 10, 1969 · N3016R · DestroyedNTSB LAX70F0153Accident · Serious injuriesClosedHughes 269B · Grnd Canyon, AZOct 9, 1969 · N9442F · Substantial damageNTSB LAX70F0168Accident · No injuriesClosedTaylorcraft BC12-D · Pacoima, CAOct 8, 1969 · N95218 · Substantial damageNTSB OAK70D0160Accident · No injuriesClosedBell 47G3B1 · Bishop, CAOct 7, 1969 · N3096G · DestroyedNTSB LAX70F0170Accident · Minor injuriesClosedNavion A · Williams, AZOct 7, 1969 · N4277K · DestroyedNTSB DEN70A0024Accident · 1 fatalClosedCessna 175 · Eureka, MTOct 7, 1969 · N9303B · DestroyedNTSB NYC70D0172Accident · No injuriesClosedMooney M20C · Middlesex, NYOct 6, 1969 · N7861V · Substantial damageNTSB ANC70D0089Accident · No injuriesClosedCessna 172 · St Mary Mssn, AKOct 6, 1969 · N5937A · Substantial damageNTSB IAD70D0096Accident · No injuriesClosedPiper PA-12 · Buckhannon, WVOct 5, 1969 · N3395M · Substantial damageNTSB ANC70F0034Accident · No injuriesClosedAeronca 11BC · Glennallen, AKOct 5, 1969 · N3261E · Substantial damageNTSB MKC70A0015Accident · 2 fatalClosedTaylorcraft BC12-D · Minneapolis, MNOct 4, 1969 · N29526 · DestroyedNTSB IAD70D0094Accident · No injuriesClosedPiper PA-28 · Buckhannon, WVOct 4, 1969 · N5409W · Substantial damageNTSB MKC70F0246Accident · No injuriesClosedBeech A23 · Coleman, SDSep 30, 1969 · N3560R · Substantial damageNTSB FTW70F0278Accident · Minor injuriesClosedCessna 140 · Pineville, ARSep 30, 1969 · N90017 · Substantial damageNTSB FTW70D0232Accident · Minor injuriesClosedGrumman G-164 · Hughes, ARSep 29, 1969 · N587Y · Substantial damageNTSB NYC70F0176Accident · Minor injuriesClosedPiper PA-28 · Waterford, CTSep 28, 1969 · N9245J · Substantial damageNTSB MKC70F0218Accident · No injuriesClosedPiper PA-22 · Cedar Falls, IASep 27, 1969 · N5970Z · Substantial damageNTSB FTW70F0231Accident · No injuriesClosedAeronca 65-CA · Mabelville, ARSep 27, 1969 · N39574 · Substantial damageNTSB ANC70F0030Accident · No injuriesClosedTaylorcraft BC12D · Fairbanks, AKSep 25, 1969 · N95786 · Substantial damageNTSB ANC70A0028Accident · No injuriesClosedBell 47G-2 · Barrow, AKSep 25, 1969 · N6751D · Substantial damageNTSB IAD70D0071Accident · No injuriesClosedPiper PA-22 · Indian Trail, NCSep 21, 1969 · N8445D · Substantial damageNTSB ANC70D0033Accident · Minor injuriesClosedPiper PA-11 · Anchorage, AKSep 21, 1969 · N4987M · Substantial damageNTSB ANC70F0067Accident · Serious injuriesClosedStinson 108-3 · Cantwell, AKSep 20, 1969 · N622C · DestroyedNTSB MKC70F0223Accident · No injuriesClosedMooney M20G · Olathe, KSSep 18, 1969 · N6944N · Substantial damageNTSB OAK70A0022Accident · No injuriesClosedPiper PA-32 · San Francisco, CASep 16, 1969 · N4137R · Substantial damageNTSB FTW70F0235Accident · No injuriesClosedCessna A188 · Wildorado, TXSep 16, 1969 · N8140V · Substantial damageNTSB DEN70A0020Accident · 2 fatalClosedTed Smith 601 · Santa Fe, NMSep 15, 1969 · N7442S · DestroyedNTSB NYC70F0132Accident · No injuriesClosedCessna 140 · Hellertown, PASep 14, 1969 · N2196N · Substantial damageNTSB MIA70F0155Accident · Serious injuriesClosedSturgeon A1 · Sumter, SCSep 14, 1969 · N2158 · DestroyedNTSB SEA70D0130Accident · No injuriesClosedCessna 170B · Agnes, ORSep 13, 1969 · N4359B · Substantial damageNTSB MKC70D0273Accident · No injuriesClosedCessna 172 · Carpio, NDSep 13, 1969 · N5672A · Substantial damageNTSB FTW70A0016Accident · No injuriesClosedBell 47G-4A · Pecan Island, LASep 13, 1969 · N4708R · Substantial damageNTSB CHI70F0232Accident · Minor injuriesClosedCessna 140 · Traverse Cty, MISep 13, 1969 · N2727N · Substantial damageNTSB NYC70D0170Accident · Minor injuriesClosedAmer Avco AA-1 · Danbury, CTSep 12, 1969 · N5649L · Substantial damageNTSB OAK70F0119Accident · No injuriesClosedBeech P-35 · Mammoth Lks, CASep 11, 1969 · N1567Z · Substantial damageNTSB CHI70D0212Accident · Minor injuriesClosedCessna 150 · Shabbona, ILSep 9, 1969 · N3516L · Substantial damageNTSB ANC70D0061Accident · No injuriesClosedPiper PA-18 · Paxson, AKSep 9, 1969 · N7998P · Substantial damageNTSB FTW70F0228Accident · No injuriesClosedPiper PA-18 · Barton, ARSep 7, 1969 · N9855D · Substantial damageNTSB FTW70F0195Accident · Minor injuriesClosedStinson 108-1 · De Quincy, LASep 7, 1969 · N8912K · DestroyedNTSB CHI70F0238Accident · No injuriesClosedCessna 177 · Antioch, ILSep 7, 1969 · N29306 · Substantial damageNTSB ANC70F0063Accident · No injuriesClosedCessna 180 · Gakona, AKSep 7, 1969 · N3125C · Substantial damage