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 FTW69A0088Accident · 1 fatalClosedCessna 310 · Somerset, TXJun 8, 1969 · N6784T · DestroyedNTSB OAK70D0026Accident · No injuriesClosedBoeing E75 · Newell, CAJun 6, 1969 · N53399 · Substantial damageNTSB LAX70D0004Accident · No injuriesClosedCessna A188 · Calipatria, CAJun 6, 1969 · N8083V · DestroyedNTSB NYC69I0108Incident · No injuriesClosedBoeing 727-95 · Boston, MAJun 4, 1969 · N1635 · Minor damageNTSB MIA70F0006Accident · Serious injuriesClosedAeronca 11CC · Glen Allan, MSJun 4, 1969 · N4417E · Substantial damageNTSB FTW69D0543Accident · No injuriesClosedPiper PA-25 · Bono, ARJun 4, 1969 · N7608Z · Substantial damageNTSB MKC70D0046Accident · No injuriesClosedPiper PA-25 · Dupree, SDJun 3, 1969 · N6184Z · Substantial damageNTSB CHI70F0028Accident · Minor injuriesClosedCessna 172 · Detroit, MIJun 3, 1969 · N8381X · Substantial damageNTSB NYC69A0107Accident · 3 fatalClosedPiper PA-32 · Greenwich, CTJun 2, 1969 · N3375W · DestroyedNTSB FTW70F0013Accident · No injuriesClosedSnow S2C · Lake Arthur, LAJun 2, 1969 · N1651S · Substantial damageNTSB FTW70D0031Accident · No injuriesClosedPiper PA-18A · Stuttgart, ARJun 2, 1969 · N3800Z · Substantial damageNTSB SEA70D0003Accident · No injuriesClosedBell 47G · Ritzville, WAJun 1, 1969 · N979B · Substantial damageNTSB NYC70F0057Accident · Serious injuriesClosedLuscombe 8A · Cross Keys, NJJun 1, 1969 · N1391K · Substantial damageNTSB MIA69D0494Accident · No injuriesClosedPiper PA-28 · Gordo, ALJun 1, 1969 · N5222W · DestroyedNTSB MIA69A0083Accident · 3 fatalClosedCessna 177 · Chamblee, GAJun 1, 1969 · N3347T · DestroyedNTSB LAX70D0012Accident · No injuriesClosedCessna 150 · S Fernando, CAJun 1, 1969 · N7004S · Substantial damageNTSB CHI70F0013Accident · Serious injuriesClosedLuscombe 8E · Beaverton, MIJun 1, 1969 · N1304B · Substantial damageNTSB NYC70F0001Accident · Minor injuriesClosedMonocoupe 90 · Taunton, MAMay 31, 1969 · N38902 · DestroyedNTSB MKC69D0476Accident · No injuriesClosedChampion 7ECA · Atlantic, IAMay 31, 1969 · N11051 · Substantial damageNTSB ANC70F0008Accident · Serious injuriesClosedPiper PA-22 · Willow, AKMay 31, 1969 · N6854B · Substantial damageNTSB NYC70D0016Accident · Minor injuriesClosedCessna 150G · Coventry, RIMay 30, 1969 · N3688J · DestroyedNTSB FTW70F0047Accident · No injuriesClosedStinson 10A · Mineral Wells, TXMay 30, 1969 · N47302 · Substantial damageNTSB FTW70F0011Accident · No injuriesClosedPiper PA-28 · Dewitt, ARMay 30, 1969 · N9318N · Substantial damageNTSB CHI70F0048Accident · No injuriesClosedCessna 175A · Barberton, OHMay 30, 1969 · N6834E · Substantial damageNTSB NYC70D0018Accident · No injuriesClosedGrumman G-164A · Chatsworth, NJMay 29, 1969 · N909X · Substantial damageNTSB DIA70F0002Accident · Minor injuriesClosedCessna 172E · Laurel, MDMay 29, 1969 · N3885S · Substantial damageNTSB ANC69A0060Accident · No injuriesClosedCessna 177 · Goodnews Bay, AKMay 29, 1969 · N29572 · Substantial damageNTSB NYC70D0006Accident · Minor injuriesClosedPiper PA-28 · Canastota, NYMay 28, 1969 · N6362W · Substantial damageNTSB CHI69F0656Accident · Serious injuriesClosedAlon A-2 · Troy, MIMay 28, 1969 · N6356V · Substantial damageNTSB MKC70D0007Accident · No injuriesClosedCallair A-6 · Bassett, NEMay 27, 1969 · N2909G · Substantial damageNTSB FTW70D0076Accident · No injuriesClosedCessna 150 · Yantis, TXMay 27, 1969 · N8786G · Substantial damageNTSB FTW69F0519Accident · No injuriesClosedGrumman G-164A · De Witt, ARMay 27, 1969 · N950X · Substantial damageNTSB UNK69X0122Accident · Serious injuriesClosedCessna 177 · Boston Bar, CanadaMay 26, 1969 · N29604 · DestroyedNTSB MKC69D0462Accident · No injuriesClosedCessna 170A · Reva, SDMay 26, 1969 · N9765A · Substantial damageNTSB DEN70D0017Accident · Minor injuriesClosedCessna 172 · Fountain, COMay 26, 1969 · N46195 · Substantial damageNTSB MKC70F0037Accident · No injuriesClosedLuscombe T-8F · Hillsboro, NDMay 25, 1969 · N1583B · Substantial damageNTSB MIA69F0473Accident · Serious injuriesClosedAer-Pegaso M-100S · Boca Raton, FLMay 25, 1969 · N64G · Substantial damageNTSB SEA70D0044Accident · Minor injuriesClosedAeronca 15AC · Burns, ORMay 23, 1969 · N1326H · Substantial damageNTSB NYC70D0022Accident · No injuriesClosedBeech 23 · Hastings, PAMay 23, 1969 · N2321L · Substantial damageNTSB NYC69F0469Accident · No injuriesClosedBeech B23 · Doylestown, PAMay 23, 1969 · N825AL · Substantial damageNTSB SEA69D0258Accident · No injuriesClosedFairchild M-62 · Bruneau, IDMay 22, 1969 · N56110 · Substantial damageNTSB CAN69ACAIOAccident · 1 fatalClosedCessna 195 · Flat Lake, CanadaMay 22, 1969 · N4350N · DestroyedNTSB FTW70D0018Accident · No injuriesClosedBoeing A75A1 · Bay City, TXMay 21, 1969 · N51442 · Substantial damageNTSB DEN70D0015Accident · No injuriesClosedAero Comdr 100 · Farson, WYMay 21, 1969 · N2942T · DestroyedNTSB FTW69D0548Accident · Minor injuriesClosedCessna 180 · New Iberia, LAMay 20, 1969 · N4698U · Substantial damageNTSB ANC70D0006Accident · No injuriesClosedPiper PA-18 · Kotzebue, AKMay 20, 1969 · N1128A · Substantial damageNTSB NYC69F0461Accident · Serious injuriesClosedPiper PA-22 · Sharon, PAMay 19, 1969 · N2426P · Substantial damageNTSB FTW69F0524Accident · Serious injuriesClosedCessna 170B · Tulsa, OKMay 18, 1969 · N2914D · Substantial damageNTSB NYC70F0086Accident · Minor injuriesClosedAeronca 7AC · Moorestown, NJMay 17, 1969 · N81744 · DestroyedNTSB MIA70D0012Accident · No injuriesClosedBell 47H-1 · Belle Glade, FLMay 17, 1969 · N7898B · Destroyed