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 FTW65A0114Accident · No injuriesClosedBoeing 707 120 · El Paso, TXMay 11, 1965 · N7521A · Substantial damageNTSB DEN65A0072Accident · No injuriesClosedHughes 269B · Grand Canyon, AZMay 10, 1965 · N9347F · Substantial damageNTSB SEA65A0061Accident · 1 fatalClosedGlobe Swift GC-1B · Lacrosse, WAMay 9, 1965 · N80610 · DestroyedNTSB NYC66F0001Accident · Minor injuriesClosedLuscombe 8E · Cicero, NYMay 9, 1965 · N2551K · DestroyedNTSB FTW66F0071Accident · No injuriesClosedGrumman G-164 · Winters, TXMay 8, 1965 · N331Y · Substantial damageNTSB OAK66D0011Accident · Minor injuriesClosedLuscombe 8A · Santa Rosa, CAMay 7, 1965 · N77950 · DestroyedNTSB OAK65F0047Accident · No injuriesClosedCessna 210 · Fresno, CAMay 7, 1965 · N9495T · Substantial damageNTSB ANC65F0017Accident · Serious injuriesClosedPiper PA-22 · Anchorage, AKMay 7, 1965 · N9330D · Substantial damageNTSB DEN66F0008Accident · Serious injuriesClosedCessna 150 · Montrose, COMay 6, 1965 · N4148U · Substantial damageNTSB OAK65D0081Accident · No injuriesClosedBoeing A-75N1 · Williams, CAMay 4, 1965 · N68467 · Substantial damageNTSB MKC65D0127Accident · No injuriesClosedTaylorcraft DCO · Pacific Junct, IAMay 3, 1965 · N57922 · DestroyedNTSB LAX65A0086Accident · No injuriesClosedBoeing B-720B · Los Angeles, CAMay 3, 1965 · N57201 · Substantial damageNTSB NYC65F0171Accident · No injuriesClosedCessna 210E · Rochester, NYMay 2, 1965 · N4959U · Substantial damageNTSB MIA66F0033Accident · Serious injuriesClosedPiper PA-22 · Suwanee, GAMay 1, 1965 · N6906B · DestroyedNTSB CHI65F0177Accident · No injuriesClosedCessna 140 · Pontiac, MIMay 1, 1965 · N76943 · Substantial damageNTSB DEN65D0094Accident · No injuriesClosedMooney M21 · Lukachukai, AZApr 30, 1965 · N1354W · Substantial damageNTSB DEN65D0080Accident · Minor injuriesClosedPiper PA-25 · Lebarge, WYApr 27, 1965 · N7375Z · Substantial damageNTSB DEN65A0068Accident · No injuriesClosedHiller UH12E · Meredith, COApr 27, 1965 · N9748C · Substantial damageNTSB CHI65A0092Accident · 3 fatalClosedNavion A · Peoria, ILApr 27, 1965 · N4506K · DestroyedNTSB ANC65A0047Accident · Serious injuriesClosedBell 47G3 · Anchorage, AKApr 27, 1965 · N8413E · DestroyedNTSB OAK66F0013Accident · Minor injuriesClosedCallair A9 · Dingville, CAApr 26, 1965 · N8287H · Substantial damageNTSB MIA65A0106Accident · No injuriesClosedBell 47G4 · Doraville, GAApr 26, 1965 · N3081G · Substantial damageNTSB LAX65F0062Accident · Serious injuriesClosedTaylorcraft BC12D · Newbury Park, CAApr 26, 1965 · N95023 · Substantial damageNTSB FTW66F0151Accident · No injuriesClosedBoeing E-75 · El Campo, TXApr 26, 1965 · N3997B · Substantial damageNTSB SEA66D0005Accident · No injuriesClosedPiper PA-22 · Helix, ORApr 25, 1965 · N4533A · Substantial damageNTSB NYC65F0199Accident · No injuriesClosedPiper J3C · North Norwich, NYApr 25, 1965 · N88158 · Substantial damageNTSB DEN66F0038Accident · No injuriesClosedPiper PA-22 · Roswell, NMApr 25, 1965 · N3634P · Substantial damageNTSB SEA65A0054Accident · 1 fatalClosedPiper PA-18 · Dell, MTApr 24, 1965 · N9457D · DestroyedNTSB OAK66F0001Accident · No injuriesClosedGrumman G-164 · Yuba City, CAApr 24, 1965 · N405Y · Substantial damageNTSB OAK66D0041Accident · No injuriesClosedCessna 170B · Quincy, CAApr 24, 1965 · N3100A · Substantial damageNTSB NYC65D0216Accident · No injuriesClosedPiper J3L · Kinzua, PAApr 24, 1965 · N35855 · Substantial damageNTSB MKC65F0131Accident · No injuriesClosedErcoupe 415CD · Osceola, MOApr 24, 1965 · N2786H · Substantial damageNTSB MIA66F0132Accident · Serious injuriesClosedGlobe 6C-1A · Augusta, GAApr 24, 1965 · N80559 · DestroyedNTSB FTW65F0031Accident · No injuriesClosedGrumman G-164 · Edcouch, TXApr 24, 1965 · N10397 · Substantial damageNTSB DEN65F0057Accident · Minor injuriesClosedPiper PA-22 · Beryl Junction, UTApr 24, 1965 · N994A · DestroyedNTSB SEA65A0052Accident · 1 fatalClosedLuscombe 8A · Mead, WAApr 23, 1965 · N28416 · DestroyedNTSB CHI65F0159Accident · No injuriesClosedCessna 182A · Phillips, WIApr 22, 1965 · N5172D · Substantial damageNTSB CHI65A0091Accident · Minor injuriesClosedHughes 269A · Columbus, OHApr 21, 1965 · N8786F · DestroyedNTSB NYC65F0140Accident · Minor injuriesClosedStinson 108-2 · Ashland, VAApr 20, 1965 · N9717K · DestroyedNTSB LAX65F0060Accident · Serious injuriesClosedGrumman G21A · Avalon, CAApr 20, 1965 · N2767A · Substantial damageNTSB FTW66F0208Accident · No injuriesClosedCessna 172D · Amarillo, TXApr 20, 1965 · N8528X · Substantial damageNTSB FTW65D0051Accident · No injuriesClosedBoeing B-75N1 · Lake Charles, LAApr 20, 1965 · N9412N · Substantial damageNTSB CHI65F0101Accident · No injuriesClosedPiper PA-28 · Fredericktown, OHApr 20, 1965 · N7552W · DestroyedNTSB NYC65F0165Accident · No injuriesClosedPiper PA-24 · Irvona, PAApr 19, 1965 · N8417P · Substantial damageNTSB DEN65D0075Accident · No injuriesClosedCessna 182 · Llaves, NMApr 19, 1965 · N9992T · Substantial damageNTSB MKC66F0130Accident · No injuriesClosedHoward DGA-15 · Basehor, KSApr 18, 1965 · N9045H · Substantial damageNTSB MIA66F0131Accident · Minor injuriesClosedPiper PA-24 · Rochelle, GAApr 18, 1965 · N7035P · Substantial damageNTSB DEN65F0060Accident · Minor injuriesClosedPiper PA-12 · Rimrock, AZApr 18, 1965 · N3814M · DestroyedNTSB CHI65F0145Accident · No injuriesClosedMooney M20 · Canton, ILApr 18, 1965 · N5533Q · Substantial damageNTSB NYC65D0205Accident · No injuriesClosedPiper J3C · Middletown, DEApr 17, 1965 · N51532 · Substantial damage