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 DIA69D0025Accident · No injuriesClosedPiper PA-24 · Ocean City, MDJul 8, 1968 · N8687P · Substantial damageNTSB MKC69F0042Accident · Minor injuriesClosedCessna 150 · Agnew, NEJul 7, 1968 · N6212R · Substantial damageNTSB MIA69D0056Accident · Minor injuriesClosedCessna 150 · Doerun, GAJul 7, 1968 · N8115S · Substantial damageNTSB MKC69D0048Accident · No injuriesClosedCessna 177 · Rushford, MNJul 6, 1968 · N30040 · Substantial damageNTSB MIA69F0115Accident · Minor injuriesClosedPiper PA-22 · Sylvania, GAJul 6, 1968 · N7837D · Substantial damageNTSB MIA69D0047Accident · No injuriesClosedCessna 172 · Labelle, FLJul 6, 1968 · N8004L · DestroyedNTSB FTW69D0101Accident · No injuriesClosedBoeing A75 · Walnut Ridge, ARJul 6, 1968 · N53414 · Substantial damageNTSB DEN69D0049Accident · No injuriesClosedPiper PA-24 · Albuquerque, NMJul 6, 1968 · N6235P · Substantial damageNTSB CHI69F0051Accident · No injuriesClosedMooney M20D · Boscobel, WIJul 6, 1968 · N6913U · Substantial damageNTSB SEA69A0001Accident · 1 fatalClosedBeech 95-B55 · Eastsound, WAJul 4, 1968 · N6014F · DestroyedNTSB FTW69F0116Accident · Serious injuriesClosedCessna 150F · Winnie, TXJul 3, 1968 · N3072X · Substantial damageNTSB CHI69F0034Accident · Minor injuriesClosedTailwind Cougar · Ypsilanti, MIJul 3, 1968 · N12045 · Substantial damageNTSB MIA69A0001Accident · 1 fatalClosedAeronca 65-TC · Moultrie, GAJul 2, 1968 · N29476 · DestroyedNTSB FTW69F0122Accident · No injuriesClosedPiper PA-25 · Coy, ARJul 2, 1968 · N4759Y · Substantial damageNTSB FTW69F0039Accident · Serious injuriesClosedHarlow S-1A · Muenster, TXJul 2, 1968 · N37205 · Substantial damageNTSB CHI69A0001Accident · 1 fatalClosedMorrisey 2150A · Lawrenceville, ILJul 2, 1968 · N5136V · DestroyedNTSB SEA69F0023Accident · Minor injuriesClosedCessna 188 · Dayton, WAJul 1, 1968 · N9775V · Substantial damageNTSB MIA69F0157Accident · Serious injuriesClosedCessna 120 · Jonesboro, GAJun 30, 1968 · N1737N · Substantial damageNTSB MIA69F0101Accident · No injuriesClosedPiper PA-25 · Salinas, PRJun 28, 1968 · N4844S · Substantial damageNTSB MIA69F0010Accident · No injuriesClosedPiper PA-18A · Louisville, MSJun 28, 1968 · N8189C · Substantial damageNTSB FTW69F0057Accident · Minor injuriesClosedCessna 140 · Naples, TXJun 28, 1968 · N76839 · DestroyedNTSB FTW69F0100Accident · No injuriesClosedCessna 172H · N.Little Rock, ARJun 27, 1968 · N2543L · Substantial damageNTSB OAK69F0040Accident · Serious injuriesClosedPiper PA-22 · Honolulu, HIJun 26, 1968 · N3662A · Substantial damageNTSB MKC68A0075Accident · Minor injuriesClosedCessna 180 · Ely, MNJun 26, 1968 · N7935V · Substantial damageNTSB NYC69F0071Accident · Minor injuriesClosedBrantly 305 · New York City, NYJun 25, 1968 · N2223U · DestroyedNTSB MIA69D0031Accident · No injuriesClosedPiper PA-18 · Montgomery, ALJun 25, 1968 · N1956A · Substantial damageNTSB LAX69D0041Accident · No injuriesClosedBell 47G3B1 · Boron, CAJun 25, 1968 · N1110J · Substantial damageNTSB LAX68A0111Accident · 4 fatalClosedBeech 95-B55 · Buena Park, CAJun 25, 1968 · N8442N · DestroyedNTSB NYC69F0038Accident · Minor injuriesClosedPiper PA-15 · Stormville, NYJun 23, 1968 · N4411H · Substantial damageNTSB MKC69D0059Accident · No injuriesClosedBell 47G-2 · Guckeen, MNJun 23, 1968 · N4735S · Substantial damageNTSB LAX68I0112Incident · No injuriesClosedBoeing 727 · Los Angeles, CAJun 23, 1968 · N972PS · Minor damageNTSB DIA69D0016Accident · No injuriesClosedPiper J-3 · Waldorf, MDJun 23, 1968 · N30589 · Substantial damageNTSB DEN69D0043Accident · No injuriesClosedBell 47G3B1 · Loa, UTJun 22, 1968 · N1357X · Substantial damageNTSB LAX69D0033Accident · No injuriesClosedBell 47G3B1 · Alpine, AZJun 21, 1968 · N73285 · Substantial damageNTSB SEA69D0008Accident · No injuriesClosedCallair A-9 · Fort Hall, IDJun 20, 1968 · N7235V · Substantial damageNTSB LAX69F0023Accident · Serious injuriesClosedCessna 150G · Van Nuys, CAJun 20, 1968 · N4037J · DestroyedNTSB DIA69F0008Accident · 1 fatalClosedBensen B-8M · West Point, VAJun 20, 1968 · N4210 · Substantial damageNTSB CHI69F0042Accident · Serious injuriesClosedLuscombe 8A · Newport, MIJun 20, 1968 · N71554 · DestroyedNTSB NYC69D0102Accident · No injuriesClosedAeronca 65-CA · E.Brookfield, MAJun 18, 1968 · N36875 · Substantial damageNTSB MKC69D0038Accident · Minor injuriesClosedPiper PA-18A · Naper, NEJun 18, 1968 · N3397B · DestroyedNTSB MIA69F0036Accident · Minor injuriesClosedMonocoupe 90AF · Gainesville, FLJun 18, 1968 · N38903 · DestroyedNTSB SEA69D0090Accident · No injuriesClosedBeech 35 · Neah Bay, WAJun 17, 1968 · N3940N · Substantial damageNTSB MIA69F0040Accident · Minor injuriesClosedFlaglor II · Gadsden, ALJun 17, 1968 · N12042 · Substantial damageNTSB MIA68A0108Accident · 2 fatalClosedPiper PA-30 · Statesboro, GAJun 17, 1968 · N7603Y · DestroyedNTSB NYC68A0133Accident · 2 fatalClosedPiper J3C-65 · Greece, NYJun 16, 1968 · N98499 · DestroyedNTSB MIA69F0073Accident · Serious injuriesClosedStinson 108-3 · Kennesaw, GAJun 16, 1968 · N635C · Substantial damageNTSB ANC69F0009Accident · Minor injuriesClosedCessna 170 · Skwentna, AKJun 16, 1968 · N2660V · Substantial damageNTSB NYC69F0058Accident · Serious injuriesClosedBeech 95-C55 · Vergennes, VTJun 15, 1968 · N9597Q · DestroyedNTSB MIA69D0037Accident · No injuriesClosedCessna 150 · Ft.Lauderdale, FLJun 14, 1968 · N8369J · Substantial damageNTSB CHI69D0035Accident · Minor injuriesClosedPiper PA-28 · Lewisburg, KYJun 12, 1968 · N4215J · Destroyed