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 MKC69F0185Accident · Serious injuriesClosedCessna 177 · St.Charles, MOSep 29, 1968 · N3235T · Substantial damageNTSB FTW69F0242Accident · No injuriesClosedCessna 177 · Pearland, TXSep 29, 1968 · N2337Y · Substantial damageNTSB ANC69D0063Accident · No injuriesClosedLake LA-4 · Fairbanks, AKSep 29, 1968 · N7604L · Substantial damageNTSB NYC69D0187Accident · No injuriesClosedStinson 108-2 · Hallstead, PASep 28, 1968 · N9372K · Substantial damageNTSB MIA69F0299Accident · Serious injuriesClosedAeronca 7AC · Lafayette, GASep 28, 1968 · N2023E · Substantial damageNTSB MIA69D0187Accident · Serious injuriesClosedPiper PA-25 · Batesville, MSSep 28, 1968 · N7331Z · DestroyedNTSB CHI69F0252Accident · Serious injuriesClosedAeronca 7AC · Lawton, MISep 28, 1968 · N83128 · Substantial damageNTSB MKC69F0182Accident · No injuriesClosedCessna 172F · Pella, IASep 27, 1968 · N8544U · Substantial damageNTSB MKC69F0180Accident · No injuriesClosedBeech 23 · Garden City, KSSep 27, 1968 · N8711M · DestroyedNTSB LAX69D0154Accident · No injuriesClosedBell 206A · Sonoita, AZSep 27, 1968 · N4039G · Substantial damageNTSB FTW69A0019Accident · 1 fatalClosedMooney M20E · Hugo, OKSep 27, 1968 · N2570W · DestroyedNTSB SEA69D0122Accident · Minor injuriesClosedBell 47G3B1 · Anaconda, MTSep 26, 1968 · N73931 · Substantial damageNTSB OAK69F0150Accident · Serious injuriesClosedBell 47G3B1 · Chowchilla, CASep 26, 1968 · N3377H · DestroyedNTSB NYC69A0041Accident · No injuriesClosedDouglas DC-3C · Teterboro, NYSep 26, 1968 · N64423 · DestroyedNTSB FTW69F0258Accident · No injuriesClosedCessna 337B · Midland, TXSep 26, 1968 · N2408S · Substantial damageNTSB FTW69F0234Accident · Serious injuriesClosedErco 415-C · Franklin, TXSep 24, 1968 · N87323 · DestroyedNTSB MKC69F0224Accident · Serious injuriesClosedTaylorcraft BC12 · Hershey, NESep 23, 1968 · N36460 · Substantial damageNTSB LAX69D0151Accident · No injuriesClosedBoeing 75 · Old River, CASep 23, 1968 · N5600 · Substantial damageNTSB NYC69A0039Accident · 2 fatalClosedPiper J3C-65 · North Canaan, CTSep 22, 1968 · N70708 · DestroyedNTSB CHI69F0264Accident · No injuriesClosedGlobe GC-1B · Wapakoneta, OHSep 22, 1968 · N80514 · Substantial damageNTSB ANC69F0060Accident · No injuriesClosedPiper PA-14 · Talkeetna, AKSep 22, 1968 · N5116H · Substantial damageNTSB FTW69F0229Accident · Serious injuriesClosedNavion B · Dallas, TXSep 21, 1968 · N5351X · Substantial damageNTSB CHI69F0256Accident · No injuriesClosedCessna 150 · Linden, MISep 21, 1968 · N7890Z · DestroyedNTSB CHI69A0033Accident · 2 fatalClosedDehavilland 104 · Richmond, OHSep 21, 1968 · N6533D · DestroyedNTSB MKC69D0222Accident · No injuriesClosedPiper PA-15 · Council Blfs, IASep 20, 1968 · N4471H · Substantial damageNTSB FTW69D0210Accident · No injuriesClosedCessna 140 · Perkins, OKSep 20, 1968 · N1699V · Substantial damageNTSB DIA69F0084Accident · No injuriesClosedPiper PA-28 · Chatham, VASep 20, 1968 · N6324J · Substantial damageNTSB DEN69F0122Accident · Minor injuriesClosedStinson 108-2 · Aurora, COSep 19, 1968 · N8096K · DestroyedNTSB NYC69A0036Accident · Serious injuriesClosedBeech TC-45G · Franklin, PASep 18, 1968 · N7200C · DestroyedNTSB DIA69D0078Accident · No injuriesClosedBell 47G · Gaithersburg, MDSep 18, 1968 · N616X · Substantial damageNTSB CHI69A0031Accident · Serious injuriesClosedCessna 402 · Paducah, KYSep 17, 1968 · N3259Q · DestroyedNTSB SEA69F0104Accident · Minor injuriesClosedCessna 310B · W-Yellowstne, MTSep 16, 1968 · N222V · DestroyedNTSB NYC69A0034Accident · Serious injuriesClosedPiper PA-28 · Easton, PASep 16, 1968 · N3742T · DestroyedNTSB FTW69D0237Accident · No injuriesClosedGrumman G-164A · Ferriday, LASep 16, 1968 · N930X · Substantial damageNTSB OAK69F0110Accident · Serious injuriesClosedCessna 140 · Rio Linda, CASep 15, 1968 · N76479 · Substantial damageNTSB NYC69A0032Accident · 1 fatalClosedCessna 150G · Endicott, NYSep 15, 1968 · N6341S · Substantial damageNTSB DEN69D0105Accident · Minor injuriesClosedCessna 182D · Dutch John, UTSep 15, 1968 · N9991T · Substantial damageNTSB CHI69F0247Accident · No injuriesClosedCessna 150 · Bargersville, INSep 15, 1968 · N23197 · Substantial damageNTSB CHI69D0328Accident · No injuriesClosedPiper PA-28 · Roselawn, INSep 15, 1968 · N7795W · Substantial damageNTSB LAX69F0111Accident · No injuriesClosedCessna 150D · Fullerton, CASep 14, 1968 · N4448U · Substantial damageNTSB FTW69F0206Accident · Minor injuriesClosedCessna 150 · Okla.City, OKSep 14, 1968 · N2277J · DestroyedNTSB DEN69F0189Accident · 2 fatalClosedPiper PA-24 · Littleton, COSep 14, 1968 · N7915P · DestroyedNTSB CHI69A0029Accident · 1 fatalClosedAeronca 65-CA · New London, OHSep 14, 1968 · N36659 · DestroyedNTSB ANC69A0017Accident · 2 fatalClosedPiper PA-18 · Anchorage, AKSep 14, 1968 · N7167D · Substantial damageNTSB FTW69F0187Accident · No injuriesClosedCessna 140 · Afton, OKSep 13, 1968 · N72438 · Substantial damageNTSB MIA69D0231Accident · No injuriesClosedCessna 150F · Titusville, FLSep 12, 1968 · N8269S · Substantial damageNTSB FTW69D0205Accident · No injuriesClosedBoeing A75 · Bradley, ARSep 12, 1968 · N151J · DestroyedNTSB DIA69A0013Accident · 1 fatalClosedMooney M20E · White Marsh, MDSep 11, 1968 · N79852 · DestroyedNTSB ANC69F0076Accident · No injuriesClosedCessna 170A · Anchorage, AKSep 10, 1968 · N9535A · Substantial damageNTSB NYC69D0174Accident · No injuriesClosedCessna 150 · Callicoon, NYSep 9, 1968 · N7047S · Substantial damage