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 MKC66F0477Accident · Serious injuriesClosedBeech 35-C33 · Slater, MOMar 15, 1966 · N6007E · DestroyedNTSB DEN66F0721Accident · Serious injuriesClosedCessna 150 · Las Cruces, NMMar 14, 1966 · N6592T · DestroyedNTSB DEN66F0247Accident · No injuriesClosedPiper PA-22 · Boulder, COMar 13, 1966 · N7139B · Substantial damageNTSB MKC66D0520Accident · No injuriesClosedEroupe 415-C · Murdo, SDMar 12, 1966 · N94330 · Substantial damageNTSB FTW66F0527Accident · No injuriesClosedCallair A-9 · Calumet, OKMar 12, 1966 · N8243H · Substantial damageNTSB DEN66A0064Accident · Serious injuriesClosedDouglas A-26B · Phoenix, AZMar 12, 1966 · N161Q · Substantial damageNTSB MKC66F0473Accident · Minor injuriesClosedCessna 140 · Miltonvale, KSMar 11, 1966 · N4125N · Substantial damageNTSB CHI66F0630Accident · No injuriesClosedAlon A-2 · Waukegan, ILMar 11, 1966 · N6357V · Substantial damageNTSB SEA66F0226Accident · No injuriesClosedPiper PA-25 · Hubbard, ORMar 10, 1966 · N7436Z · Substantial damageNTSB MKC66D0533Accident · No injuriesClosedPiper PA-28 · Hopkins, MNMar 10, 1966 · N9053J · Substantial damageNTSB CHI66F0627Accident · No injuriesClosedBeech B-95 · Barrington, ILMar 10, 1966 · N1117K · DestroyedNTSB CHI66F0680Accident · No injuriesClosedChampion 7KC · Middleton, WIMar 9, 1966 · N8977R · Substantial damageNTSB LAX66A0068Accident · 1 fatalClosedCessna 182B · Santa Cruz Is, CAMar 8, 1966 · N7254E · DestroyedNTSB FTW66F0554Accident · Minor injuriesClosedCessna 170A · Stuttgart, ARMar 8, 1966 · N1790D · Substantial damageNTSB DEN66A0063Accident · No injuriesClosedPiper PA-24 · Hanksville, UTMar 7, 1966 · N8325P · Substantial damageNTSB DEN66D0274Accident · No injuriesClosedPiper PA-18 · Seely, WYMar 5, 1966 · N7537K · Substantial damageNTSB OAK66F0218Accident · Serious injuriesClosedPiper PA-32 · Fresno, CAMar 4, 1966 · N3423W · DestroyedNTSB OAK66F0215Accident · No injuriesClosedPiper PA-18 · Modesto, CAMar 3, 1966 · N2422A · DestroyedNTSB NYC66F0530Accident · Minor injuriesClosedPiper PA-28 · Chester, WVMar 3, 1966 · N5586W · Substantial damageNTSB FTW66A0075Accident · No injuriesClosedBell Carson · Wetmore, TXMar 3, 1966 · N865Z · DestroyedNTSB DEN66F0232Accident · No injuriesClosedBeechcraft 33 · Woods Cross, UTMar 2, 1966 · N8502M · Substantial damageNTSB MIA66F0454Accident · No injuriesClosedGlobe GC-1B · Venice, FLMar 1, 1966 · N3797K · Substantial damageNTSB MIA66A0095Accident · 1 fatalClosedCessna 172 · Portland, TNFeb 27, 1966 · N8177U · DestroyedNTSB LAX66F0272Accident · No injuriesClosedMonocoupe 90 · Long Beach, CAFeb 27, 1966 · N520W · Substantial damageNTSB LAX66A0064Accident · No injuriesClosedBeech D18S · Reno, NVFeb 27, 1966 · N1209 · Substantial damageNTSB FTW66D0548Accident · No injuriesClosedReynolds JCR-1 · Big Spring, TXFeb 27, 1966 · N9001Z · Substantial damageNTSB MKC66F0499Accident · No injuriesClosedPiper PA-22 · Columbia, MOFeb 26, 1966 · N7804D · Substantial damageNTSB FTW66F0547Accident · No injuriesClosedCessna 185C · Morgan City, LAFeb 26, 1966 · N2673Z · Substantial damageNTSB OAK66D0220Accident · No injuriesClosedBeechcraft 95 · San Francisco, CAFeb 25, 1966 · N2080C · Substantial damageNTSB LAX66A0275Accident · Serious injuriesClosedBeech K35 · Santa Ynez, CAFeb 25, 1966 · N3063C · Substantial damageNTSB FTW66A0083Accident · No injuriesClosedBell 47G-2 · Lake Charles, LAFeb 25, 1966 · N8425E · Substantial damageNTSB NYC66F0548Accident · No injuriesClosedPiper PA-22 · Hamburg, NYFeb 24, 1966 · N3148Z · Substantial damageNTSB NYC66F0544Accident · Minor injuriesClosedAero Comdr 500-B · Hagerstown, MDFeb 24, 1966 · N9388R · Substantial damageNTSB MKC66F0478Accident · Minor injuriesClosedGlobe GC-1B · White Bear Lk, MNFeb 24, 1966 · N2387B · Substantial damageNTSB MIA66F0441Accident · Minor injuriesClosedPiper J3C · Columbia, SCFeb 20, 1966 · N30551 · Substantial damageNTSB CHI66D0603Accident · No injuriesClosedAeronca 7AC · Piqua, OHFeb 20, 1966 · N82280 · Substantial damageNTSB DEN66D0223Accident · Minor injuriesClosedCulver V · Tucson, AZFeb 19, 1966 · N44516 · Substantial damageNTSB NYC66F0525Accident · Serious injuriesClosedNavion B · Meriden, CTFeb 17, 1966 · N808 · DestroyedNTSB MKC66F0415Accident · Serious injuriesClosedN.American P-51 · Columbia, MOFeb 17, 1966 · N5162V · DestroyedNTSB LAX66F0282Accident · No injuriesClosedCessna 180 · Gabbs, NVFeb 17, 1966 · N4764U · Substantial damageNTSB MKC66D0433Accident · No injuriesClosedPiper J-3 · Bijou Hills, SDFeb 13, 1966 · N70542 · Substantial damageNTSB CHI66F0584Accident · Serious injuriesClosedErco 415-C · Baraboo, WIFeb 13, 1966 · N99772 · DestroyedNTSB SEA66F0213Accident · Minor injuriesClosedPiper PA-22 · Vancouver, WAFeb 12, 1966 · N898A · Substantial damageNTSB NYC66F0513Accident · Minor injuriesClosedCessna 140 · Bellefonte, PAFeb 12, 1966 · N72921 · Substantial damageNTSB LAX66F0242Accident · No injuriesClosedCessna 150 · Lompoc, CAFeb 12, 1966 · N7161X · Substantial damageNTSB OAK66F0228Accident · 1 fatalClosedPiper PA-30 · San Francisco, CAFeb 10, 1966 · N7891Y · DestroyedNTSB DEN66D0218Accident · No injuriesClosedCessna 210E · Roswel, NMFeb 10, 1966 · N4908U · Substantial damageNTSB OAK66F0191Accident · Minor injuriesClosedSwift GC-1B · Caruthers, CAFeb 9, 1966 · N78292 · Substantial damageNTSB LAX66F0254Accident · Minor injuriesClosedPiper J3C-65 · Palm Desert, CAFeb 9, 1966 · N92339 · Substantial damageNTSB LAX66D0265Accident · No injuriesClosedMooney M20C · Fullerton, CAFeb 9, 1966 · N6749U · Substantial damage