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 LAX66F0180Accident · Minor injuriesClosedGlobe GC-1B · Huntington B, CAMar 2, 1965 · N21P · DestroyedNTSB NYC65F0073Accident · Serious injuriesClosedCessna 310D · Tipton, PAMar 1, 1965 · N6965T · DestroyedNTSB OAK65F0014Accident · No injuriesClosedBellanca 14-13 · Sacramento, CAFeb 27, 1965 · N86804 · Substantial damageNTSB DEN65F0029Accident · Serious injuriesClosedPiper PA-22 · Tucson, AZFeb 24, 1965 · N5213Z · DestroyedNTSB MKC65F0089Accident · Minor injuriesClosedPiper PA-22 · Rogersville, MOFeb 20, 1965 · N6833B · Substantial damageNTSB UNK65X1436Accident · No injuriesClosedStinson 108-2 · Columbus, GAFeb 19, 1965 · N8019K · Substantial damageNTSB FTW66D0001Accident · No injuriesClosedCessna 150 · Washburn, TXFeb 18, 1965 · N4437U · DestroyedNTSB UNK65X1432Accident · No injuriesClosedPiper PA22 · Ardmore, OKFeb 17, 1965 · N4579Z · Substantial damageNTSB FTW65A0080Accident · 2 fatalClosedTaylorcraft BC-12D · Arlington, TXFeb 14, 1965 · N39213 · DestroyedNTSB MIA65A0082Accident · 2 fatalClosedCurtiss C-46A · Miami, FLFeb 13, 1965 · YS-012C · DestroyedNTSB DEN65F0035Accident · Minor injuriesClosedCessna U9K · Broomfield, COFeb 12, 1965 · N4436C · Substantial damageNTSB UNK65X1399Accident · No injuriesClosedPiper PA-22 · Tampa, FLFeb 6, 1965 · N3931P · Substantial damageNTSB UNK65X1391Accident · Minor injuriesClosedBeechraft G35 · Grants, NMFeb 4, 1965 · N151B · Substantial damageNTSB NYC65F0060Accident · Minor injuriesClosedPiper PA-30 · Laconia, NHFeb 4, 1965 · N7267Y · DestroyedNTSB SEA65D0011Accident · No injuriesClosedPiper PA24 · Challis, IDFeb 2, 1965 · N9003T · Substantial damageNTSB LAX66D0029Accident · No injuriesClosedPiper PA-23 · Fullerton, CAFeb 2, 1965 · N5528Y · Substantial damageNTSB UNK65X1382Accident · No injuriesClosedPiper PA-22 · Seminole, TXFeb 1, 1965 · N8131C · Substantial damageNTSB UNK65X1374Accident · No injuriesClosedPiper PA-18 · Anchorage, AKJan 30, 1965 · N3909Z · Substantial damageNTSB NYC65F0034Accident · Minor injuriesClosedCessna 172 · Mitchellville, MDJan 30, 1965 · N5656T · DestroyedNTSB UNK65X1355Accident · 4 fatalClosedCessna 336 · Mexico City, MexicoJan 27, 1965 · N3878U · DestroyedNTSB UNK65X1354Accident · 4 fatalClosedCessna 210 · Mexico City, MexicoJan 27, 1965 · N3878U · DestroyedNTSB NYC65F0001Accident · Serious injuriesClosedNavion B · Flushing, NYJan 27, 1965 · N5309K · Substantial damageNTSB MKC65F0027Accident · No injuriesClosedPiper PA-28 · Sublette, KSJan 24, 1965 · N7985W · Substantial damageNTSB UNK65X1317Accident · No injuriesClosedCessna 310G · Tampa, FLJan 19, 1965 · N172V · Substantial damageNTSB UNK65X1314Accident · No injuriesClosedBeechcraft B35 · Warrington, FLJan 19, 1965 · N5213C · Substantial damageNTSB CHI65F0002Accident · Minor injuriesClosedMooney M20D · Indianapolis, INJan 19, 1965 · N1967Y · Substantial damageNTSB UNK65X1310Accident · No injuriesClosedCallair A5 · Margate, FLJan 18, 1965 · N2931G · DestroyedNTSB MKC65D0022Accident · No injuriesClosedPiper PA-11 · Redwood Falls, MNJan 18, 1965 · N4643M · Substantial damageNTSB CHI65F0003Accident · No injuriesClosedBeechraft B55 · Negaunee, MIJan 18, 1965 · N3001S · Substantial damageNTSB MIA65I0075Incident · No injuriesClosedBoeing707 227 · Miami, FLJan 17, 1965 · N7074 · No damageNTSB FTW65A0074Accident · 2 fatalClosedPiper J3 · Kaplan, LAJan 17, 1965 · N7404 · DestroyedNTSB DEN65D0013Accident · No injuriesClosedBeechraft M23 · Eads, COJan 17, 1965 · N2369Q · Substantial damageNTSB SEA65F0004Accident · Minor injuriesClosedSwift GC-1B · Troutdale, ORJan 16, 1965 · N3761K · Substantial damageNTSB LAX65F0042Accident · No injuriesClosedCessna 182 · Joshua Tree, CAJan 13, 1965 · N8791X · Substantial damageNTSB UNK65X1276Accident · Minor injuriesClosedPiper PA22 · Winston Salem, NCJan 12, 1965 · N5947Z · DestroyedNTSB MIA65I0074Incident · No injuriesClosedBoeing B-727 · Miami, FLJan 12, 1965 · N8116N · Minor damageNTSB UNK65X1273Accident · Serious injuriesClosedBeechraft A-35 · Hearne, TXJan 11, 1965 · N8645A · DestroyedNTSB MKC65F0010Accident · No injuriesClosedMooney M20 · Pierre, SDJan 10, 1965 · N5206B · Substantial damageNTSB MIA65A0072Accident · 2 fatalClosedCessna 210 · Ocean Sprgs, MSJan 10, 1965 · N9565T · DestroyedNTSB LAX65F0075Accident · No injuriesClosedAero Cmdr 560E · Temecula, CAJan 8, 1965 · N344P · Substantial damageNTSB UNK65X1244Accident · Minor injuriesClosedRepublic RC3 · Gardenville, NYJan 7, 1965 · N87513 · Substantial damageNTSB UNK65X1242Accident · No injuriesClosedCessna 120 · Fresno, CAJan 6, 1965 · N1825N · Substantial damageNTSB UNK65X1236Accident · No injuriesClosedStinson 108-2 · Tulare, CAJan 6, 1965 · N382C · Substantial damageNTSB UNK65X1222Accident · No injuriesClosedPiper J3 · White Marsh, MDJan 3, 1965 · N23327 · Substantial damageNTSB SEA65F0014Accident · No injuriesClosedCessna 180 · Burns, ORJan 3, 1965 · N6452X · Substantial damageNTSB CHI65F0013Accident · No injuriesClosedBeechraft H35 · Fraser, MIJan 3, 1965 · N5556D · Substantial damageNTSB UNK65X1210Accident · No injuriesClosedPiper PA16 · Woodstown, NJJan 1, 1965 · N5346H · Substantial damageNTSB UNK65X1200Accident · Minor injuriesClosedNavion A · Lorain, OHDec 31, 1964 · N8765H · DestroyedNTSB UNK65X1184Accident · No injuriesClosedTaylorcraft DCO · Vinita, OKDec 29, 1964 · N50605 · Substantial damageNTSB UNK65X1182Accident · Serious injuriesClosedStinson 108-3 · Van Nuys, CADec 29, 1964 · N6641M · Destroyed