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 UNK64X1696Accident · Minor injuriesClosedPiper PA-23 · Albuquerque, NMMay 10, 1964 · N72F · Substantial damageNTSB UNK64X1694Accident · No injuriesClosedPiper J3 · Minneapolis, MNMay 10, 1964 · N33023 · Substantial damageNTSB UNK64X1688Accident · No injuriesClosedPiper PA-22 · Mulino, ORMay 9, 1964 · N8644D · Substantial damageNTSB UNK64X1683Accident · No injuriesClosedStearman A75N1 · Gueydan, LAMay 9, 1964 · N3939C · DestroyedNTSB UNK64X1664Accident · No injuriesClosedCessna 170 · Baker, NVMay 8, 1964 · N4290V · Substantial damageNTSB UNK64X1637Accident · No injuriesClosedMooney M18LA · E Windsor, CTMay 6, 1964 · N4089 · Substantial damageNTSB UNK64X1625Accident · 2 fatalClosedCessna 140 · Winslow, AZMay 5, 1964 · N72621 · DestroyedNTSB UNK64X1615Accident · No injuriesClosedPiper PA30 · San Juan, PRMay 4, 1964 · N7322Y · DestroyedNTSB UNK64X1607Accident · Minor injuriesClosedCessna 120 · Coalville, UTMay 4, 1964 · N81070 · Substantial damageNTSB UNK64X1598Accident · No injuriesClosedCessna 150 · Clayton, INMay 3, 1964 · N5517E · Substantial damageNTSB UNK64X1590Accident · No injuriesClosedLuscombe 8A · Van Wert, OHMay 3, 1964 · N1246B · Substantial damageNTSB UNK64X1568Accident · Serious injuriesClosedCessna 170 · Bonner Spgs, KSMay 2, 1964 · N2627V · DestroyedNTSB UNK64X1562Accident · No injuriesClosedBellanca 1413 · Buffalo, WYMay 2, 1964 · N86722 · Substantial damageNTSB UNK64X1561Accident · No injuriesClosedBeechcraft AT11 · Orangeville, NYMay 2, 1964 · N30Q · Substantial damageNTSB UNK64X1558Accident · No injuriesClosedCessna 140 · Watervliet, MIMay 2, 1964 · N76276 · Substantial damageNTSB UNK64X1543Accident · No injuriesClosedGlobe Swift GC1B · Knoxville, TNMay 1, 1964 · N78230 · Substantial damageNTSB UNK64X1542Accident · Minor injuriesClosedGrumann G164 · Wadsworth, TXMay 1, 1964 · N579Y · DestroyedNTSB UNK64X1513Accident · Serious injuriesClosedBeechcraft C18S · Beaufort, NCApr 29, 1964 · N6419C · DestroyedNTSB UNK64X1511Accident · No injuriesClosedNavion NAV4 · Friendswood, TXApr 29, 1964 · N4483K · Substantial damageNTSB UNK64X1496Accident · No injuriesClosedPiper PA24 · Howard Lake, MNApr 28, 1964 · N6293Z · Substantial damageNTSB UNK64X1485Accident · No injuriesClosedNavion · Torrance, CAApr 27, 1964 · N8922H · Substantial damageNTSB UNK64X1476Accident · No injuriesClosedBrantly B2 · Thetford, VTApr 26, 1964 · N4997C · Substantial damageNTSB UNK64X1475Accident · Minor injuriesClosedBeechcraft T34 · Midland, VAApr 26, 1964 · N7973A · DestroyedNTSB UNK64X1460Accident · No injuriesClosedHughes 269A · Duncan Mills, CAApr 25, 1964 · N8811F · Substantial damageNTSB UNK64X1459Accident · No injuriesClosedBenson B2 · Rosemount, MNApr 25, 1964 · N1076Z · Substantial damageNTSB UNK64X1420Accident · No injuriesClosedCessna 150 · Samsula, FLApr 23, 1964 · N2078Z · Substantial damageNTSB UNK64X1414Accident · Minor injuriesClosedAeronca 65C · Excelsior Spgs, MOApr 22, 1964 · N21380 · Substantial damageNTSB UNK64X1380Accident · No injuriesClosedErcoupe 415C · East Moriches, NYApr 19, 1964 · N87288 · DestroyedNTSB UNK64X1379Accident · No injuriesClosedPiper J3F · Tola, KSApr 19, 1964 · N35877 · Substantial damageNTSB UNK64X1375Accident · No injuriesClosedTaylorcraft BC65 · Balston Spa, NYApr 19, 1964 · N21253 · Substantial damageNTSB UNK64X1369Accident · No injuriesClosedBeechraft D-18S · Uqayyil, Saudi ArabiaApr 19, 1964 · N9320Z · Substantial damageNTSB UNK64X1368Accident · Minor injuriesClosedErcoupe 415CD · Friendly, MDApr 18, 1964 · N94755 · DestroyedNTSB UNK64X1367Accident · Serious injuriesClosedCulver V · Crestview, FLApr 18, 1964 · N44515 · DestroyedNTSB UNK64X1344Accident · No injuriesClosedBeechraft F-35 · Sinaloa, MexicoApr 17, 1964 · N3371C · DestroyedNTSB UNK64X1343Accident · Minor injuriesClosedCessna 180 · Dix, NEApr 16, 1964 · N2197Z · DestroyedNTSB UNK64X1337Accident · No injuriesClosedCessna 170B · North Judson, INApr 16, 1964 · N1897C · Substantial damageNTSB UNK64X1304Accident · 1 fatalClosedPiper PA-30 · Sebastian, FLApr 13, 1964 · N7336Y · DestroyedNTSB UNK64X1302Accident · No injuriesClosedBeechcraft 35 · Beaverton, ORApr 13, 1964 · N3854N · Substantial damageNTSB UNK64X1286Accident · No injuriesClosedErcoupe 415C · Oxford, PAApr 12, 1964 · N94633 · Substantial damageNTSB UNK64X1251Accident · No injuriesClosedGrumman G164 · Grimes, CAApr 11, 1964 · N599Y · Substantial damageNTSB UNK64X1242Accident · No injuriesClosedLuscombe 8F · Middletown, NYApr 10, 1964 · N1862K · Substantial damageNTSB UNK64X1241Accident · Serious injuriesClosedCessna 310C · Cincinnati, OHApr 9, 1964 · N1782H · Substantial damageNTSB UNK64X1229Accident · Serious injuriesClosedAeronca 7AC · Ames, OKApr 8, 1964 · N83867 · Substantial damageNTSB UNK64X1228Accident · 1 fatalClosedBeechcraft T34B · Medicine Bow, WYApr 8, 1964 · N6623C · DestroyedNTSB UNK64X1224Accident · Minor injuriesClosedTaylorcraft BL65 · Mullen, NEApr 8, 1964 · N22268 · Substantial damageNTSB UNK64X1213Accident · Serious injuriesClosedCessna 195 · York, SCApr 7, 1964 · N2185C · DestroyedNTSB UNK64X1206Accident · No injuriesClosedCessna 185 · Bull River, AKApr 6, 1964 · N2545Z · Substantial damageNTSB UNK64X1201Accident · No injuriesClosedLuscombe 8A · Lombard, ILApr 6, 1964 · N71955 · DestroyedNTSB UNK64X1196Accident · 2 fatalClosedBeechcraft 35 · West Milford, NJApr 5, 1964 · N2745V · DestroyedNTSB UNK64X1192Accident · No injuriesClosedSwift GC1B · Latrobe, PAApr 5, 1964 · N78320 · Substantial damage