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 FTW69F0411Accident · No injuriesClosedAero Comdr 200-D · Bogalusa, LAMar 25, 1969 · N2954T · Substantial damageNTSB CHI69A0083Accident · No injuriesClosedCessna 402 · Chicago, ILMar 25, 1969 · N3207Q · Substantial damageNTSB MIA69D0450Accident · No injuriesClosedPiper PA-22 · Merritt Is., FLMar 24, 1969 · N2277A · Substantial damageNTSB NYC69F0407Accident · No injuriesClosedCessna 177 · Connellsville, PAMar 23, 1969 · N3339T · Substantial damageNTSB CHI69F0629Accident · Serious injuriesClosedPiper PA-28 · Chrisman, ILMar 23, 1969 · N6559J · Substantial damageNTSB CHI69F0560Accident · No injuriesClosedPiper PA-22 · Galveston, INMar 23, 1969 · N6177D · Substantial damageNTSB CHI69F0557Accident · No injuriesClosedCessna 210-5 · Mt Sterling, OHMar 23, 1969 · N8429Z · Substantial damageNTSB CHI69F0547Accident · Serious injuriesClosedPiper PA-22 · Phillipsburg, OHMar 23, 1969 · N7036B · Substantial damageNTSB MKC69F0356Accident · Minor injuriesClosedCessna 210 · Augusta, KSMar 21, 1969 · N9429T · Substantial damageNTSB MIA69F0425Accident · Minor injuriesClosedPiper J-3 · Laceys Spring, ALMar 20, 1969 · N58378 · Substantial damageNTSB MIA69F0393Accident · No injuriesClosedCessna 150J · Chamblee, GAMar 19, 1969 · N50721 · Substantial damageNTSB FTW69F0455Accident · No injuriesClosedPiper PA-32 · San Antonio, TXMar 18, 1969 · N3754W · Substantial damageNTSB FTW69D0466Accident · No injuriesClosedPiper PA-18 · Happy, TXMar 18, 1969 · N1353A · Substantial damageNTSB NYC69A0089Accident · Serious injuriesClosedCessna 337B · Jfk Intl Apt, NYMar 17, 1969 · N2416S · DestroyedNTSB FTW69F0424Accident · No injuriesClosedPiper PA-22 · Tulsa, OKMar 16, 1969 · N5218Z · Substantial damageNTSB CHI69A0082Accident · 1 fatalClosedYankee AA-1 · Benton Harbr, MIMar 16, 1969 · N5639L · DestroyedNTSB NYC69F0347Accident · Minor injuriesClosedStinson 108-2 · Hampden, MEMar 15, 1969 · N391C · Substantial damageNTSB OAK70O0011Accident · Serious injuriesClosedBell 204-B · Ben Hoa, VMMar 13, 1969 · N1304X · Substantial damageNTSB FTW69F0433Accident · No injuriesClosedPiper PA-22 · Bay City, TXMar 13, 1969 · N3409A · Substantial damageNTSB FTW69F0426Accident · No injuriesClosedBeech 23 · Claremore, OKMar 13, 1969 · N2309L · Substantial damageNTSB MKC69D0393Accident · No injuriesClosedErco 415-C · Leawood, KSMar 12, 1969 · N94710 · Substantial damageNTSB CHI69A0080Accident · 1 fatalClosedPiper PA-23 · Bowling Green, KYMar 12, 1969 · N3017P · DestroyedNTSB MKC69F0364Accident · Minor injuriesClosedMooney M20G · Kenett, MOMar 11, 1969 · N3714N · Substantial damageNTSB CHI69F0541Accident · Serious injuriesClosedPiper PA-28 · South Haven, MIMar 11, 1969 · N6211Z · DestroyedNTSB ANC69A0043Accident · Minor injuriesClosedPiper PA-32 · Naknek, AKMar 10, 1969 · N3268W · Substantial damageNTSB ANC69A0042Accident · No injuriesClosedDehavilland DHC-3 · Port Moller, AKMar 10, 1969 · N98T · Substantial damageNTSB SEA69A0048Accident · 1 fatalClosedBreguet 905A · Issaquah, WAMar 9, 1969 · N280M · DestroyedNTSB DIA69D0155Accident · Minor injuriesClosedCessna 177 · Chester, WVMar 9, 1969 · N30229 · Substantial damageNTSB CHI69F0520Accident · No injuriesClosedLuscombe 8A · Fraser, MIMar 9, 1969 · N2964K · Substantial damageNTSB SEA69D0202Accident · No injuriesClosedPiper PA-25 · Pomeroy, WAMar 8, 1969 · N7263Z · Substantial damageNTSB LAX69F0283Accident · No injuriesClosedErcoupe 415-CD · Sunset Beach, CAMar 8, 1969 · N3454H · Substantial damageNTSB MKC69F0371Accident · Minor injuriesClosedCessna 177 · Trenton, NEMar 3, 1969 · N3174T · Substantial damageNTSB CHI69A0077Accident · 2 fatalClosedCessna 170 · Troy, MIMar 3, 1969 · N4056V · DestroyedNTSB MKC69F0348Accident · No injuriesClosedCessna 172 · Salem, MOMar 2, 1969 · N2098Y · Substantial damageNTSB MIA69D0400Accident · No injuriesClosedCessna 170B · Lakeland, FLMar 2, 1969 · N2744D · Substantial damageNTSB OAK69D0238Accident · No injuriesClosedCallair A-9 · Linden, CAMar 1, 1969 · N8238H · Substantial damageNTSB MKC69D0415Accident · No injuriesClosedBensen B-8M · Cloquet, MNMar 1, 1969 · N1361 · Substantial damageNTSB MIA69D0394Accident · No injuriesClosedCessna 150 · Morrow, GAMar 1, 1969 · N3908U · Substantial damageNTSB DEN69F0194Accident · Minor injuriesClosedAero Comdr 100 · Albuquerque, NMMar 1, 1969 · N3865X · Substantial damageNTSB CHI69F0552Accident · No injuriesClosedPiper PA-28 · Ft.Wayne, INMar 1, 1969 · N8926W · Substantial damageNTSB LAX69F0295Accident · 1 fatalClosedPiper PA-28 · Twin Peaks, CAFeb 28, 1969 · N7157R · DestroyedNTSB FTW69F0469Accident · Serious injuriesClosedCessna 150 · Corpus Chrsti, TXFeb 27, 1969 · N6867F · DestroyedNTSB MKC69F0341Accident · Serious injuriesClosedCessna 150 · Dodge City, KSFeb 26, 1969 · N5513E · DestroyedNTSB FTW69F0423Accident · No injuriesClosedGrumman G-164A · Ville Platte, LAFeb 26, 1969 · N967X · DestroyedNTSB OAK69D0276Accident · Minor injuriesClosedHughes 269B · Panoche, CAFeb 25, 1969 · N13470 · Substantial damageNTSB LAX69F0284Accident · No injuriesClosedCessna 182B · Compton, CAFeb 25, 1969 · N7266E · Substantial damageNTSB DIA69F0146Accident · No injuriesClosedMooney M20G · Sandston, VAFeb 25, 1969 · N3593X · Substantial damageNTSB DEN69F0183Accident · Serious injuriesClosedBeech A35 · Truth Consq, NMFeb 23, 1969 · N619B · Substantial damageNTSB NYC69F0328Accident · No injuriesClosedEaa Biplane B-2 · Monroeville, PAFeb 22, 1969 · N1119B · Substantial damageNTSB NYC69F0327Accident · Minor injuriesClosedCessna 177 · Maytown, PAFeb 22, 1969 · N3102T · Destroyed