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 NYC65A0146Accident · No injuriesClosedBell 47G-4 · East Syracuse, NYJun 10, 1965 · N73996 · Substantial damageNTSB CHI66F0469Accident · Minor injuriesClosedLuscombe 8A · East St Louis, ILJun 10, 1965 · N1407K · Substantial damageNTSB UNK65X1497Accident · No injuriesClosedPiper PA-18 · El Rodeo, MexicoJun 9, 1965 · N1055A · Substantial damageNTSB FTW66F0064Accident · No injuriesClosedAero Cmdr 500A · Denver City, TXJun 9, 1965 · N78372 · Substantial damageNTSB SEA66F0038Accident · No injuriesClosedPiper J3L · Bloomfield, MTJun 8, 1965 · N30948 · Substantial damageNTSB FTW66F0092Accident · No injuriesClosedGrumman G-164 · Eunice, LAJun 7, 1965 · N459Y · Substantial damageNTSB LAX65F0079Accident · No injuriesClosedCessna 170B · Buena Park, CAJun 6, 1965 · N4619C · Substantial damageNTSB CHI65A0102Accident · No injuriesClosedDuane Rock B8M · Benton Harbor, MIJun 6, 1965 · N261D · Substantial damageNTSB CHI65A0100Accident · 2 fatalClosedPiper PA-22 · Warren, OHJun 5, 1965 · N1496A · DestroyedNTSB ANC65F0015Accident · No injuriesClosedLake C-2 · Anchorage, AKJun 5, 1965 · N274B · Substantial damageNTSB ANC65F0014Accident · No injuriesClosedChampion 7EC · Fairbanks, AKJun 5, 1965 · N4363C · DestroyedNTSB FTW66F0045Accident · No injuriesClosedNavion B · Fort Stockton, TXJun 4, 1965 · N91695 · Substantial damageNTSB ANC66F0009Accident · Minor injuriesClosedMaule M-4 · Valdez, AKJun 4, 1965 · N4721T · Substantial damageNTSB MKC66F0027Accident · No injuriesClosedBeechcraft A-35 · Warrensburg, MOJun 2, 1965 · N8736A · Substantial damageNTSB OAK66F0005Accident · Serious injuriesClosedLuscombe 8A · Half Moon Bay, CAMay 31, 1965 · N1526K · DestroyedNTSB LAX66F0031Accident · No injuriesClosedCessna 170B · Hinkley, CAMay 31, 1965 · N2217D · Substantial damageNTSB FTW66D0062Accident · No injuriesClosedBoeing E-75 · Eunice, LAMay 31, 1965 · N9984H · DestroyedNTSB DEN65F0097Accident · Minor injuriesClosedCessna 172 · Sundance, WYMay 30, 1965 · N4077F · Substantial damageNTSB DEN65D0099Accident · No injuriesClosedSchweizer TG-2 · Aspen, COMay 30, 1965 · N67660 · Substantial damageNTSB CHI66F0066Accident · No injuriesClosedMooney M20C · Pikeville, KYMay 30, 1965 · N6508U · DestroyedNTSB CHI66D0030Accident · Minor injuriesClosedSwift GC-1B · Linton, INMay 30, 1965 · N3291K · DestroyedNTSB MIA66F0018Accident · No injuriesClosedFairchild 24C8C · Rockledge, FLMay 29, 1965 · N15350 · DestroyedNTSB ANC65A0052Accident · No injuriesClosedDouglas DC-3C · Nikolski, AKMay 29, 1965 · N91016 · Substantial damageNTSB DEN65F0103Accident · Minor injuriesClosedPiper PA-22 · Logan, UTMay 27, 1965 · N3449A · Substantial damageNTSB OAK66F0006Accident · No injuriesClosedBoeing B-75N1 · Willows, CAMay 26, 1965 · N68421 · Substantial damageNTSB NYC66F0290Accident · No injuriesClosedHelio MK2 · Youngwood, PAMay 25, 1965 · N5451E · Substantial damageNTSB LAX66F0012Accident · No injuriesClosedCessna 172 · Montello, NVMay 25, 1965 · N3616S · Substantial damageNTSB FTW66D0081Accident · No injuriesClosedCessna 305A · Almyra, ARMay 24, 1965 · N10455 · Substantial damageNTSB FTW65A0119Incident · No injuriesClosedDouglas DC-6A · Altus, OKMay 24, 1965 · N34956 · Substantial damageNTSB ANC65D0013Accident · No injuriesClosedPiper PA-20 · Yanert Valley, AKMay 24, 1965 · N7343K · Substantial damageNTSB NYC65F0226Accident · No injuriesClosedPiper PA-12 · Baltimore, MDMay 23, 1965 · N4454M · Substantial damageNTSB CHI65D0185Accident · No injuriesClosedRyan ST-3KR · Willard, OHMay 23, 1965 · N470S · Substantial damageNTSB NYC65F0212Accident · Serious injuriesClosedAeronca 7AC · Plymouth, CTMay 22, 1965 · N2615E · Substantial damageNTSB CHI65F0199Accident · No injuriesClosedStinson 108 · Walbridge, OHMay 22, 1965 · N8194K · Substantial damageNTSB CHI65F0186Accident · No injuriesClosedCessna 140 · Cincinnati, OHMay 21, 1965 · N89026 · Substantial damageNTSB DEN65D0088Accident · No injuriesClosedPiper PA-11 · Pinedale, WYMay 20, 1965 · N78838 · Substantial damageNTSB SEA66F0045Accident · Minor injuriesClosedPiper PA-18A · Culver, ORMay 19, 1965 · N8568D · DestroyedNTSB MIA66F0053Accident · No injuriesClosedPiper PA-23 · Oxford, ALMay 19, 1965 · N5102Y · Substantial damageNTSB NYC66F0163Accident · Serious injuriesClosedPiper PA12 · Trumansburg, NYMay 18, 1965 · N3467M · Substantial damageNTSB NYC66F0037Accident · Minor injuriesClosedPiper PA-28 · Bainbridge, MDMay 18, 1965 · N5697W · Substantial damageNTSB FTW65A0116Accident · No injuriesClosedHughes 269B · Lubbock, TXMay 18, 1965 · N9306F · Substantial damageNTSB NYC65F0238Accident · No injuriesClosedStinson 108 · Kingwood, WVMay 16, 1965 · N97709 · DestroyedNTSB CAN65AF367Accident · 1 fatalClosedPiper PA-23 · Gander Nf, CanadaMay 16, 1965 · N5725Y · DestroyedNTSB NYC65F0184Accident · No injuriesClosedPiper PA-22 · Trexlertown, PAMay 14, 1965 · N2314P · DestroyedNTSB NYC65F0186Accident · No injuriesClosedBoeing A75-N1 · Warwick, NYMay 13, 1965 · N51807 · Substantial damageNTSB MKC65D0151Accident · No injuriesClosedCessna 180F · Nekoma, NDMay 13, 1965 · N2166Z · Substantial damageNTSB MIA65D0110Accident · No injuriesClosedPiper PA-25 · Dania, FLMay 13, 1965 · N7364Z · Substantial damageNTSB FTW66F0039Accident · Serious injuriesClosedPiper PA-25 · Maysville, OKMay 13, 1965 · N6596Z · DestroyedNTSB FTW66F0026Accident · No injuriesClosedPiper PA-25 · Lockett, TXMay 13, 1965 · N6109Z · Substantial damageNTSB CHI65F0154Accident · No injuriesClosedCessna 140 · Fraser, MIMay 13, 1965 · N2167N · Substantial damage