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 MKC68F0577Accident · No injuriesClosedPiper PA-25 · Ft.Clark, NDApr 17, 1968 · N7017Z · Substantial damageNTSB OAK68F0361Accident · Minor injuriesClosedPiper PA-28 · Selma, CAApr 15, 1968 · N6373W · Substantial damageNTSB FTW68D0552Accident · No injuriesClosedGrumman G-164 · Katy, TXApr 15, 1968 · N658Y · Substantial damageNTSB SEA68F0322Accident · Serious injuriesClosedCessna 140 · Auburn, WAApr 14, 1968 · N73027 · DestroyedNTSB NYC68D0498Accident · No injuriesClosedBeech A23 · Cape Vincent, NYApr 14, 1968 · N3535R · Substantial damageNTSB LAX68F0408Accident · Minor injuriesClosedWaco UPF-7 · Mesa, AZApr 14, 1968 · N32136 · Substantial damageNTSB FTW68A0088Accident · 2 fatalClosedChampion 7GCAA · Jacksboro, TXApr 13, 1968 · N9592S · DestroyedNTSB DIA68F0207Accident · No injuriesClosedPiper J5A · College Park, MDApr 13, 1968 · N38710 · Substantial damageNTSB MKC69D0026Accident · No injuriesClosedPiper J3C-65 · St Cloud, MNApr 11, 1968 · N20149 · Substantial damageNTSB CHI69F0271Accident · No injuriesClosedCessna 337 · Milwaukee, WIApr 11, 1968 · N2363S · Substantial damageNTSB OAK68F0333Accident · No injuriesClosedPiper PA-28 · Fair Oaks, CAApr 10, 1968 · N3905T · Substantial damageNTSB MKC68F0509Accident · No injuriesClosedPiper PA-22 · Franklin, NEApr 9, 1968 · N2334P · Substantial damageNTSB MKC68F0557Accident · No injuriesClosedPiper PA-18A · Bucyrus, NDApr 8, 1968 · N3674P · Substantial damageNTSB NYC68F0449Accident · Minor injuriesClosedChampion 7EC · So.Plainfield, NJApr 7, 1968 · N8550E · Substantial damageNTSB FTW69F0017Accident · No injuriesClosedPiper PA-24 · Mountain Home, ARApr 7, 1968 · N8603P · Substantial damageNTSB NYC68F0435Accident · Serious injuriesClosedBeech A23 · New Castle, PAApr 6, 1968 · N3643Q · Substantial damageNTSB NYC68F0418Accident · Serious injuriesClosedGlasflugel H-301 · Mechanicsburg, PAApr 2, 1968 · N2415 · Substantial damageNTSB SEA68F0268Accident · Minor injuriesClosedPiper PA-22 · Emmett, IDMar 31, 1968 · N9055D · Substantial damageNTSB FTW68F0526Accident · Minor injuriesClosedCallair A-5 · Crystal City, TXMar 31, 1968 · N9664C · DestroyedNTSB FTW68D0543Accident · No injuriesClosedPiper PA-24 · Ralls, TXMar 30, 1968 · N5945P · Substantial damageNTSB FTW68A0084Accident · 1 fatalClosedBrantly 305 · Orange, TXMar 30, 1968 · N2212U · DestroyedNTSB DEN68F0230Accident · Minor injuriesClosedBeech 35-A33 · Denver, COMar 30, 1968 · N381Z · Substantial damageNTSB OAK68A0063Accident · 3 fatalClosedBeech 35 · San Jose, CAMar 29, 1968 · N8709A · DestroyedNTSB NYC68A0113Accident · 2 fatalClosedLake LA-4 · Sanford, MEMar 29, 1968 · N7601L · DestroyedNTSB LAX68F0385Accident · No injuriesClosedPiper PA-28 · Santa Paula, CAMar 28, 1968 · N8555W · Substantial damageNTSB CHI68F0670Accident · Minor injuriesClosedBeech A23 · Roselle, ILMar 25, 1968 · N3538R · Substantial damageNTSB LAX68A0092Accident · 3 fatalClosedMooney M20E · Borego Sprngs, CAMar 24, 1968 · N3387X · DestroyedNTSB ANC68D0103Accident · Minor injuriesClosedPiper PA-18 · Kotzebue, AKMar 23, 1968 · N9860D · Substantial damageNTSB MIA68F0450Accident · No injuriesClosedCessna 172 · Powell, TNMar 22, 1968 · N5966R · DestroyedNTSB MKC68D0590Accident · No injuriesClosedPiper PA-25 · Nevada, MOMar 19, 1968 · N6079Z · Substantial damageNTSB CHI68F0663Accident · No injuriesClosedPiper PA-28 · Findlay, OHMar 19, 1968 · N5950W · Substantial damageNTSB LAX68D0366Accident · Minor injuriesClosedTaylorcraft BC12-D · Marana, AZMar 18, 1968 · N39931 · Substantial damageNTSB NYC68F0438Accident · No injuriesClosedBeech 23 · West Mifflin, PAMar 17, 1968 · N8716M · Substantial damageNTSB CHI68F0699Accident · No injuriesClosedBeech 23 · Lorain, OHMar 17, 1968 · N2301Z · Substantial damageNTSB SEA68D0269Accident · No injuriesClosedPiper PA-25 · Moscow, IDMar 16, 1968 · N4733Y · Substantial damageNTSB LAX68A0088Accident · No injuriesClosedBensen B-8 · Adelanto, CAMar 16, 1968 · N1697 · Substantial damageNTSB CHI68F0709Accident · No injuriesClosedAeronca 65-TC · Lockport, ILMar 16, 1968 · N29413 · Substantial damageNTSB MIA68F0449Accident · No injuriesClosedPiper PA-25 · Eupora, MSMar 14, 1968 · N7367Z · Substantial damageNTSB MIA68F0527Accident · Minor injuriesClosedAero Cmdr 500-A · Jeckyll Island, GAMar 12, 1968 · N9374R · Substantial damageNTSB MIA68F0451Accident · Minor injuriesClosedPiper PA-28 · Homestead, FLMar 11, 1968 · N4340J · Substantial damageNTSB MIA68D0478Accident · No injuriesClosedCessna 177C · Immokalee, FLMar 10, 1968 · N2252Y · Substantial damageNTSB FTW68F0507Accident · No injuriesClosedAlon A-2 · Castroville, TXMar 10, 1968 · N5649F · Substantial damageNTSB MIA68F0470Accident · No injuriesClosedBeech A23 · Landrum, SCMar 8, 1968 · N5997S · Substantial damageNTSB NYC68O0105Incident · No injuriesClosedBoeing 707 · Jamaica, NYMar 7, 1968 · N7551A · No damageNTSB CHI68A0080Accident · 1 fatalClosedAeronca 7AC · Franklin, WIMar 7, 1968 · N85100 · DestroyedNTSB MKC68D0515Accident · No injuriesClosedGlobe GC-1B · Lawson, MOMar 6, 1968 · N80663 · Substantial damageNTSB MKC68D0488Accident · Minor injuriesClosedAeronca 7AC · Macon, MOMar 5, 1968 · N1551E · Substantial damageNTSB FTW68F0496Accident · Serious injuriesClosedStinson L-5 · Bonham, TXMar 5, 1968 · N62452 · DestroyedNTSB DEN68A0043Accident · No injuriesClosedBeech K35 · Crownpoint, NMMar 5, 1968 · N3067C · Substantial damageNTSB OAK68D0310Accident · Minor injuriesClosedCessna 172 · Huron, CAMar 3, 1968 · N6378E · Substantial damage