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 CHI69A0055Accident · Serious injuriesClosedBeech D18S · Inkster, MINov 18, 1968 · N629B · DestroyedNTSB LAX69D0193Accident · No injuriesClosedCessna 172B · Pearblossom, CANov 17, 1968 · N7852X · Substantial damageNTSB FTW69A0033Accident · 1 fatalClosedPiper J3C-65 · Temple, OKNov 17, 1968 · N70753 · DestroyedNTSB CHI69F0361Accident · No injuriesClosedMooney M20E · Louisville, KYNov 17, 1968 · N5731Q · Substantial damageNTSB ANC69A0030Accident · No injuriesClosedCessna 180D · Manokotak, AKNov 16, 1968 · N8606X · Substantial damageNTSB MKC69F0284Accident · No injuriesClosedCessna 172 · Carrollton, MONov 14, 1968 · N4097F · Substantial damageNTSB CHI69D0358Accident · No injuriesClosedCessna 172 · Roselle, ILNov 12, 1968 · N2444Y · Substantial damageNTSB MKC69F0254Accident · No injuriesClosedBeech V35 · Des Moines, IANov 11, 1968 · N3798Q · Substantial damageNTSB NYC69F0272Accident · Minor injuriesClosedPitts SPL S-1A · Frankfort, NYNov 9, 1968 · N3107 · DestroyedNTSB MKC69A0029Accident · 1 fatalClosedBeech A65 · Wichita, KSNov 7, 1968 · F-BRAR · DestroyedNTSB OAK69A0029Accident · Serious injuriesClosedCessna 337 · Visalia, CANov 6, 1968 · N6203F · DestroyedNTSB MKC69F0251Accident · No injuriesClosedBeech A23-19 · Dallas Centr, IANov 5, 1968 · N6068N · Substantial damageNTSB SEA69D0141Accident · No injuriesClosedLuscombe 8A · Sandpoint, IDNov 4, 1968 · N2087K · Substantial damageNTSB NYC69F0238Accident · No injuriesClosedCessna 170 · Auburn, MENov 3, 1968 · N2621V · Substantial damageNTSB IAD69F0104Accident · Serious injuriesClosedPiper J4E · Sharpsburg, NCNov 3, 1968 · N38011 · Substantial damageNTSB DEN69D0139Accident · No injuriesClosedCessna 177 · Englewood, CONov 1, 1968 · N2335Y · Substantial damageNTSB ANC69D0079Accident · Minor injuriesClosedPiper PA-32 · Sand Point, AKNov 1, 1968 · N4021W · DestroyedNTSB OAK69D0162Accident · Minor injuriesClosedStearman A75 · Honokowai, HIOct 30, 1968 · N67802 · DestroyedNTSB NYC69A0049Accident · Minor injuriesClosedBeech 95-D55 · Waterville, MEOct 29, 1968 · N304HA · DestroyedNTSB MKC69F0274Accident · Minor injuriesClosedCessna 337B · Fullerton, NEOct 29, 1968 · N2334S · Substantial damageNTSB DIA69D0103Accident · Minor injuriesClosedPiper PA-22 · Chadbourn, NCOct 29, 1968 · N8004E · Substantial damageNTSB MKC69F0223Accident · No injuriesClosedErcoupe 415-C · South Haven, KSOct 28, 1968 · N37139 · Substantial damageNTSB MIA69D0272Accident · No injuriesClosedCessna 170 · Pickens, SCOct 27, 1968 · N4117V · Substantial damageNTSB MKC69F0235Accident · No injuriesClosedGlobe GC-1B · Washburn, IAOct 26, 1968 · N3764K · Substantial damageNTSB DEN69F0166Accident · No injuriesClosedCessna 310C · Broomfield, COOct 25, 1968 · N1813H · Substantial damageNTSB NYC69F0256Accident · Serious injuriesClosedPiper J3 · Shirley, MAOct 20, 1968 · N42841 · Substantial damageNTSB MIA69F0228Accident · 1 fatalClosedPitts S1C · Tampa, FLOct 20, 1968 · N6969 · DestroyedNTSB MIA69F0211Accident · Serious injuriesClosedCessna 172 · Lithonia, GAOct 20, 1968 · N4105L · Substantial damageNTSB LAX69F0174Accident · Minor injuriesClosedBeech F35 · Calexico, CAOct 20, 1968 · N4275B · DestroyedNTSB CHI69F0346Accident · Serious injuriesClosedCessna 177 · Stambaugh, MIOct 20, 1968 · N3145T · DestroyedNTSB MKC69A0024Accident · 2 fatalClosedBeech 95 · Kansas City, KSOct 15, 1968 · N8217D · DestroyedNTSB MIA69F0440Accident · No injuriesClosedCallair A-9B · Meigs, GAOct 15, 1968 · N7640V · DestroyedNTSB MKC69D0230Accident · No injuriesClosedCessna 150 · Alma, KSOct 13, 1968 · N8895G · Substantial damageNTSB FTW69F0252Accident · Minor injuriesClosedCessna 120 · Dallas, TXOct 13, 1968 · N72767 · Substantial damageNTSB DEN69F0141Accident · No injuriesClosedCessna 185 · Veyo, UTOct 13, 1968 · N5700M · Substantial damageNTSB MKC69F0244Accident · Minor injuriesClosedCessna 177 · Whte Bear Lk, MNOct 12, 1968 · N2210Y · Substantial damageNTSB FTW69F0251Accident · Serious injuriesClosedPiper PA-18 · Anahuac, TXOct 12, 1968 · N2893Z · Substantial damageNTSB SEA69F0130Accident · Minor injuriesClosedTaylorcraft A · Bellevue, WAOct 10, 1968 · N19647 · Substantial damageNTSB MIA69F0201Accident · Serious injuriesClosedGlobe GC-1B · Gulfport, MSOct 9, 1968 · N2323B · DestroyedNTSB OAK69A0021Accident · 4 fatalClosedDehavilland DH-104 · Las Vegas, NVOct 8, 1968 · N4040B · DestroyedNTSB OAK69D0173Accident · No injuriesClosedBeech A23 · San L Obispo, CAOct 7, 1968 · N1450L · Substantial damageNTSB OAK69D0167Accident · Minor injuriesClosedAeronca 11AC · Schellville, CAOct 7, 1968 · N9214E · Substantial damageNTSB NYC69F0237Accident · No injuriesClosedPiper PA-23 · Neptune, NJOct 7, 1968 · N5426Y · Substantial damageNTSB MIA69F0192Accident · No injuriesClosedPiper L-4J · Memphis, TNOct 7, 1968 · N66332 · Substantial damageNTSB DEN69D0140Accident · No injuriesClosedCessna 172 · Gillette, WYOct 7, 1968 · N1638F · Substantial damageNTSB FTW69A0021Accident · 1 fatalClosedPiper PA-22 · Borger, TXOct 6, 1968 · N2642A · DestroyedNTSB DEN69A0016Accident · 2 fatalClosedBeech A35 · Edgerton, WYOct 6, 1968 · N8770A · DestroyedNTSB MIA69F0224Accident · No injuriesClosedBeech 35 · Opa Locka, FLOct 3, 1968 · N80433 · Substantial damageNTSB CHI69D0310Accident · No injuriesClosedCessna 150 · Clintonville, WIOct 1, 1968 · N7235S · Substantial damageNTSB LAX69D0147Accident · No injuriesClosedCessna 188 · Brawley, CASep 30, 1968 · N8097V · Substantial damage