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 UNK65X1178Accident · Serious injuriesClosedTempco D-16 · Madison, MSDec 28, 1964 · N4366K · Substantial damageNTSB UNK65X1173Accident · Serious injuriesClosedCessna 205 · Littleton, CODec 28, 1964 · N8204Z · Substantial damageNTSB UNK65X1157Accident · No injuriesClosedLuscombe 8E · Auburn, CADec 27, 1964 · N2779K · Substantial damageNTSB UNK65X1155Accident · Minor injuriesClosedBeechraft 35 · Waterford, MIDec 27, 1964 · N2741V · DestroyedNTSB UNK65X1150Accident · 4 fatalClosedBellanca 14-13 · Detroit, MIDec 26, 1964 · N86878 · DestroyedNTSB UNK65X1145Accident · Serious injuriesClosedLuscombe 8A · Grand Haven, MIDec 26, 1964 · N1038K · DestroyedNTSB UNK65X1137Accident · No injuriesClosedCessna 172F · St Louis, MODec 26, 1964 · N8238U · Substantial damageNTSB UNK65X1117Accident · 3 fatalClosedLockheed L-1049 · San Francisco, CADec 24, 1964 · N6915C · DestroyedNTSB UNK65X1087Accident · No injuriesClosedBeechraft 35B33 · Gregory, SDDec 22, 1964 · N1548Z · Substantial damageNTSB UNK65X1085Accident · No injuriesClosedPiper PA22 · Andrews, TXDec 22, 1964 · N8764D · DestroyedNTSB UNK65X1082Incident · No injuriesClosedBoeing 707 351C · Travis Afb, CADec 21, 1964 · N356U · Minor damageNTSB UNK65X1079Accident · No injuriesClosedCessna 337 · Wichita, KSDec 21, 1964 · N2119X · Substantial damageNTSB UNK65X1061Accident · No injuriesClosedPiper PA24 · Bunnell, FLDec 19, 1964 · N6407P · Substantial damageNTSB UNK65X1035Accident · No injuriesClosedPiper PA28 · Old Town, MEDec 17, 1964 · N6413W · Substantial damageNTSB UNK65X1025Accident · No injuriesClosedStinson 108-1 · Groveland, FLDec 16, 1964 · N8645K · Substantial damageNTSB UNK65X0996Accident · Minor injuriesClosedCessna 185 · Buras, LADec 13, 1964 · N2690Z · Substantial damageNTSB UNK65X0981Accident · Serious injuriesClosedBeechcraft H-35 · Bladenboro, NCDec 11, 1964 · N5510D · DestroyedNTSB UNK65X0963Accident · Serious injuriesClosedTaylorcraft BC12D · Albany, MODec 9, 1964 · N95752 · Substantial damageNTSB UNK65X0948Accident · Serious injuriesClosedCessna 182 · Belzoni, MSDec 7, 1964 · N3062Y · DestroyedNTSB UNK65X0937Accident · Minor injuriesClosedCessna 182 · Calif Valley, CADec 6, 1964 · N3719U · Substantial damageNTSB UNK65X0925Accident · No injuriesClosedCessna 185 · Bethel, AKDec 5, 1964 · N9826X · DestroyedNTSB UNK65X0911Incident · No injuriesClosedLockheed L-749A · Dayton, OHDec 3, 1964 · N6008C · No damageNTSB UNK65X0869Accident · 1 fatalClosedCessna 182A · Loma Alta, TXNov 28, 1964 · N6125B · DestroyedNTSB UNK65X0850Accident · 1 fatalClosedBell 47J2 · Los Angeles, CANov 27, 1964 · N7396A · DestroyedNTSB UNK65X0840Accident · 1 fatalClosedAeronca 7DC · Eagle River, AKNov 26, 1964 · N4391E · Substantial damageNTSB UNK65X0818Accident · No injuriesClosedCessna 172 · Mesa, WANov 24, 1964 · N7362T · DestroyedNTSB UNK65X0803Accident · No injuriesClosedChampion 7FC · Kendall, FLNov 22, 1964 · N9840Y · Substantial damageNTSB UNK65X0780Accident · No injuriesClosedPiper PA-12 · Atchison, KSNov 21, 1964 · N3889M · Substantial damageNTSB UNK65X0775Accident · No injuriesClosedCessna 170A · Chapel Hill, NCNov 21, 1964 · N9738A · Substantial damageNTSB UNK65X0768Accident · No injuriesClosedCurtiss C-46A · Inkster, MINov 20, 1964 · N3971B · DestroyedNTSB UNK65X0748Accident · No injuriesClosedPiper PA22 · Cookeville, TNNov 17, 1964 · N5362Z · Substantial damageNTSB UNK65X0733Accident · No injuriesClosedPiper PA24 · Lake City, FLNov 16, 1964 · N7796P · Substantial damageNTSB UNK65X0727Accident · 4 fatalClosedPiper PA22 · Beaverton, MINov 15, 1964 · N2553P · DestroyedNTSB UNK65X0718Accident · No injuriesClosedPiper J3C · Billings, MTNov 15, 1964 · N6988H · Substantial damageNTSB UNK65X0701Accident · Minor injuriesClosedTaylorcraft DC065 · Emlenton, PANov 14, 1964 · N57731 · DestroyedNTSB UNK65X0684Accident · No injuriesClosedTaylorcraft BC12D · Anchorage, AKNov 13, 1964 · N5200M · Substantial damageNTSB UNK65X0673Accident · No injuriesClosedCessna 172 · Oxnard, CANov 12, 1964 · N7501X · Substantial damageNTSB UNK65X0672Accident · No injuriesClosedPiper PA-23 · St Louis, MONov 12, 1964 · N5194Y · Substantial damageNTSB UNK65X0670Accident · No injuriesClosedPiper PA-18A · Eureka, AKNov 12, 1964 · N9140D · DestroyedNTSB UNK65X0665Accident · 4 fatalClosedLockheed L-18 · Freeport, Burkina FasoNov 12, 1964 · N171Q · DestroyedNTSB UNK65X0656Accident · No injuriesClosedCessna 210 · Knoxville, IANov 11, 1964 · N9657T · Substantial damageNTSB UNK65X0651Accident · No injuriesClosedCessna 150 · Ramey Afb, PRNov 11, 1964 · N4275U · Substantial damageNTSB UNK65X0647Accident · No injuriesClosedBeechcraft T34 · Westhampton, NYNov 10, 1964 · N4189A · Substantial damageNTSB UNK65X0591Accident · No injuriesClosedPiper PA24 · Corona, NMNov 7, 1964 · N5985P · Substantial damageNTSB UNK65X0586Accident · 2 fatalClosedTaylorcraft DCO-65 · Port Ewen, NYNov 6, 1964 · N46406 · Substantial damageNTSB UNK65X0584Accident · Serious injuriesClosedPiper PA-11 · Louisburg, NCNov 6, 1964 · N4869M · Substantial damageNTSB UNK65X0582Accident · Minor injuriesClosedBoeing PT17 · Hakalau, HINov 6, 1964 · N67999 · DestroyedNTSB UNK65X0572Accident · No injuriesClosedNavy N3N · Imperial, CANov 5, 1964 · N45234 · DestroyedNTSB UNK65X0543Accident · No injuriesClosedStinson L5 · Castroville, CANov 3, 1964 · N66881 · Substantial damageNTSB UNK65X0531Accident · No injuriesClosedMooney 20C · Indianapolis, INNov 2, 1964 · N6846U · Substantial damage