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 NYC66F0330Accident · Minor injuriesClosedLuscombe 8A · Brewer, MEOct 24, 1965 · N1269K · Substantial damageNTSB MKC66F0283Accident · Minor injuriesClosedBeechcraft 35-33 · Marshall, MOOct 24, 1965 · N938T · DestroyedNTSB LAX66D0173Accident · No injuriesClosedPiper PA-22 · Fullerton, CAOct 24, 1965 · N2476A · Substantial damageNTSB FTW66A0038Accident · 1 fatalClosedLuscombe 8A · Guthrie, OKOct 24, 1965 · N39015 · DestroyedNTSB MIA66F0314Accident · No injuriesClosedCessna 172 · Ludowici, GAOct 21, 1965 · N7367T · Substantial damageNTSB OAK66F0132Accident · Serious injuriesClosedBell 47J2A · Lihue, HIOct 20, 1965 · N3072G · DestroyedNTSB DEN66D0161Accident · No injuriesClosedMooney M20C · Mormon Lake, AZOct 20, 1965 · N6758U · Substantial damageNTSB FTW66F0373Accident · No injuriesClosedCallair A-9 · Goodlet, TXOct 19, 1965 · N3605G · Substantial damageNTSB UNK66X0105Accident · 1 fatalClosedBeechcraft 35 · La Pesca, MexicoOct 17, 1965 · N1488G · DestroyedNTSB SEA66F0156Accident · Minor injuriesClosedBriegleb BG-7 · Rexburg, IDOct 17, 1965 · N4565K · Substantial damageNTSB MKC66F0275Accident · No injuriesClosedMooney M20C · Carbondale, KSOct 17, 1965 · N1374W · Substantial damageNTSB MIA66S0002Accident · Minor injuriesClosedPiper PA-23 · Punta Caucedo, DROct 17, 1965 · N4082P · Substantial damageNTSB CHI66A0037Accident · 2 fatalClosedNavion A · Spencer, INOct 17, 1965 · N4954K · DestroyedNTSB ANC66D0100Accident · No injuriesClosedTaylorcraft BC12-D · Mekoryuk, AKOct 17, 1965 · N43375 · Substantial damageNTSB CHI66A0039Accident · No injuriesClosedBeechcraft SNB-5 · Bainbridge, OHOct 16, 1965 · N2201S · Substantial damageNTSB OAK66D0144Accident · No injuriesClosedBeech G35 · Santa Rosa, CAOct 15, 1965 · N4628D · Substantial damageNTSB MKC66F0297Accident · No injuriesClosedMooney M20B · Mission, SDOct 15, 1965 · N74747 · Substantial damageNTSB FTW66F0343Accident · No injuriesClosedBoeing B-75N1 · Palacios, TXOct 15, 1965 · N63708 · Substantial damageNTSB FTW66F0341Accident · No injuriesClosedPiper PA-24 · El Pas, TXOct 15, 1965 · N8800P · Substantial damageNTSB MKC66A0026Accident · 3 fatalClosedCessna 172E · Kansas City, MOOct 14, 1965 · N7745U · DestroyedNTSB FTW66F0340Accident · Minor injuriesClosedBoeing E-75 · Hereford, TXOct 14, 1965 · N53394 · Substantial damageNTSB SEA66F0152Accident · No injuriesClosedCessna 170B · Silverdale, WAOct 13, 1965 · N3122A · Substantial damageNTSB MKC66A0025Accident · 1 fatalClosedCessna 180 · Bemidji, MNOct 13, 1965 · N2281C · DestroyedNTSB SEA66F0173Accident · Minor injuriesClosedBeechcraft M23 · Parkrose, OROct 10, 1965 · N8787M · Substantial damageNTSB MIA66F0251Accident · Minor injuriesClosedCessna 172E · Cabo Rojo, PROct 10, 1965 · N5462T · Substantial damageNTSB CHI66F0333Accident · Minor injuriesClosedPiper PA-22 · Wixom, MIOct 10, 1965 · N8749C · Substantial damageNTSB MKC66F0248Accident · Minor injuriesClosedPiper PA-12 · Lemars, IAOct 9, 1965 · N4035M · Substantial damageNTSB FTW66D0366Accident · No injuriesClosedPiper J3C · Comfort, TXOct 9, 1965 · N31027 · Substantial damageNTSB DEN66D0122Accident · No injuriesClosedPiper J-3 · Woodruff, UTOct 9, 1965 · N42420 · Substantial damageNTSB CHI66A0034Accident · 2 fatalClosedNavion A · Beaver Isle, MIOct 9, 1965 · N8887H · DestroyedNTSB NYC66F0305Accident · Minor injuriesClosedRyan ST-3KR · Laurel, MDOct 8, 1965 · N1144 · Substantial damageNTSB UNK66X0101Accident · Minor injuriesClosedPiper PA-18 · Alexis Crk, CanadaOct 5, 1965 · N5139V · Substantial damageNTSB MKC66F0264Accident · No injuriesClosedCessna 310I · Wichita, KSOct 5, 1965 · N8037M · Substantial damageNTSB CAN65AF386Accident · Serious injuriesClosedPiper PA-18 · Atlin Lake, CanadaOct 5, 1965 · N5139Y · Substantial damageNTSB ANC66D0071Accident · No injuriesClosedChampion 7GCB · Iliama, AKOct 3, 1965 · N8995R · Substantial damageNTSB DEN66D0137Accident · Minor injuriesClosedBellanca 14-19 · Payson, AZOct 2, 1965 · N9812B · DestroyedNTSB CHI66F0345Accident · No injuriesClosedErcoupe 415C · Wooster, OHOct 2, 1965 · N2184H · Substantial damageNTSB CHI66D0365Accident · Minor injuriesClosedCessna 195 · Fraser, MIOct 2, 1965 · N9300A · Substantial damageNTSB NYC66I0042Incident · No injuriesClosedBoeing B-727 · Newark, NJSep 29, 1965 · N7029U · No damageNTSB NYC66D0246Accident · Minor injuriesClosedTaylorcraft BC-65 · Jamaica, VTSep 28, 1965 · N36316 · Substantial damageNTSB FTW66F0313Accident · No injuriesClosedAero Cmdr 500B · Addison, TXSep 27, 1965 · N353CC · Substantial damageNTSB NYC66A0061Accident · 1 fatalClosedBeechcraft G-35 · Binghamton, NYSep 26, 1965 · N4557D · DestroyedNTSB NYC66A0010Accident · 3 fatalClosedPiper PA-22 · E.Hartford, CTSep 26, 1965 · N8120C · DestroyedNTSB ANC66D0069Accident · No injuriesClosedCessna 180 · Talkeetna, AKSep 26, 1965 · N7715A · DestroyedNTSB MKC66F0279Accident · Minor injuriesClosedCessna 150 · Bloomsdale, MOSep 25, 1965 · N4391U · DestroyedNTSB DEN66A0022Accident · No injuriesClosedBrantly B2B · Denver, COSep 25, 1965 · N2160U · Substantial damageNTSB MIA66F0306Accident · Minor injuriesClosedChampion 7AC · Tallahassee, FLSep 24, 1965 · N81411 · Substantial damageNTSB NYC66F0319Accident · No injuriesClosedHelio MK2 · Shippenville, PASep 23, 1965 · N5456E · Substantial damageNTSB FTW66F0312Accident · Serious injuriesClosedCessna 150 · Westwego, LASep 23, 1965 · N4323U · Substantial damageNTSB CHI66D0438Accident · Minor injuriesClosedTaylorcraft BC12-D · Cedar Grove, INSep 23, 1965 · N5182M · Substantial damage