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 UNK64X4013Accident · Serious injuriesClosedBell 47G3 · Elk River, IDSep 16, 1964 · N8417E · DestroyedNTSB UNK64X4010Accident · No injuriesClosedPiper J-3 · Anchorage, AKSep 16, 1964 · N98787 · Substantial damageNTSB UNK64X4008Accident · No injuriesClosedBeechcraft E18S · Ft Lauderdale, FLSep 16, 1964 · N75200 · Substantial damageNTSB UNK64X4004Accident · No injuriesClosedSnow S2C · Huron, CASep 16, 1964 · N1656S · Substantial damageNTSB UNK64X3995Accident · Minor injuriesClosedGrumman G164 · Shellnan, GASep 15, 1964 · N597Y · Substantial damageNTSB UNK64X3977Accident · Serious injuriesClosedPiper J3C · Jonesboro, TNSep 13, 1964 · N6131H · Substantial damageNTSB UNK64X3975Accident · No injuriesClosedAeronca 7AC · West Newfield, MESep 13, 1964 · N84837 · Substantial damageNTSB UNK64X3964Accident · No injuriesClosedTaylorcraft BC65 · Quartz Hill, CASep 13, 1964 · N27445 · DestroyedNTSB UNK64X3961Accident · No injuriesClosedPiper PA28 · Woolstock, IASep 13, 1964 · N5286W · Substantial damageNTSB UNK64X3953Accident · 1 fatalClosedLuscombe 8C · Marquette, WISep 13, 1964 · N28582 · DestroyedNTSB UNK64X3948Accident · No injuriesClosedPiper J3C · Veazie, MESep 12, 1964 · N3336N · Substantial damageNTSB UNK64X3939Accident · No injuriesClosedPiper PA22 · Endicott, NYSep 12, 1964 · N4353A · DestroyedNTSB UNK64X3926Accident · No injuriesClosedErcoupe 415D · Apple Valley, CASep 12, 1964 · N2441H · Substantial damageNTSB UNK64X3901Accident · No injuriesClosedBeechcraft B35 · Camden, ARSep 10, 1964 · N5021C · Substantial damageNTSB UNK64X3899Accident · No injuriesClosedPiper PA15 · Fruitvale, TXSep 10, 1964 · N4533H · Substantial damageNTSB UNK64X3895Accident · No injuriesClosedAeronca 7AC · Firth, IDSep 10, 1964 · N85635 · Substantial damageNTSB UNK64X3891Accident · No injuriesClosedBell 47G2 · Earlimart, CASep 9, 1964 · N2874B · Substantial damageNTSB UNK64X3877Accident · No injuriesClosedBell 47G4 · Canton, MSSep 8, 1964 · N3082G · Substantial damageNTSB UNK64X3869Accident · Minor injuriesClosedCessna 140 · Mt Pleasant, PASep 8, 1964 · N140FC · Substantial damageNTSB UNK64X3868Accident · No injuriesClosedGrumman G-44 · Terney Lake, CanadaSep 8, 1964 · N148M · Substantial damageNTSB UNK64X3857Accident · Minor injuriesClosedBoeing B75N1 · Olyphant, ARSep 7, 1964 · N60955 · Substantial damageNTSB UNK64X3846Accident · No injuriesClosedPiper PA18 · Plumwood, OHSep 7, 1964 · N230SU · DestroyedNTSB UNK64X3834Accident · 1 fatalClosedMooney M20C · Alamosa, COSep 6, 1964 · N6815U · DestroyedNTSB UNK64X3817Accident · No injuriesClosedPiper PA24 · Princeton, NJSep 6, 1964 · N6074P · Substantial damageNTSB UNK64X3815Accident · No injuriesClosedCessna 172E · Flippin, ARSep 6, 1964 · N5427T · Substantial damageNTSB UNK64X3814Accident · No injuriesClosedBeechcraft A23 · Springer, NMSep 6, 1964 · N8777M · Substantial damageNTSB UNK64X3811Accident · No injuriesClosedDehavilland 5A · Las Vegas, NVSep 6, 1964 · N4913V · Substantial damageNTSB UNK64X3803Accident · 1 fatalClosedErcoupe 415C · Columbus, INSep 5, 1964 · N2120H · Substantial damageNTSB UNK64X3783Accident · No injuriesClosedStinson 108-3 · Tolland, CTSep 5, 1964 · N687C · Substantial damageNTSB UNK64X3779Accident · No injuriesClosedHughes 269A · Wasco, CASep 4, 1964 · N8809F · Substantial damageNTSB UNK64X3756Accident · No injuriesClosedSnow S2A · Roaring Sprgs, TXSep 3, 1964 · N4973C · Substantial damageNTSB UNK64X3749Accident · No injuriesClosedFairchild 24J · Ottumwa, IASep 3, 1964 · N19177 · Substantial damageNTSB UNK64X3746Accident · No injuriesClosedNavy N3N3 · Gilbert, AZSep 2, 1964 · N45273 · Substantial damageNTSB UNK64X3740Accident · Serious injuriesClosedRepublic RC3 · Eliott Key, FLSep 2, 1964 · N6356K · DestroyedNTSB UNK64X3733Accident · No injuriesClosedBoeing E75N1 · Hazen, ARSep 2, 1964 · N69858 · Substantial damageNTSB UNK64X3726Accident · No injuriesClosedStinson 108-3 · Fabens Tex, TXSep 1, 1964 · N971C · Substantial damageNTSB UNK64X3718Accident · Minor injuriesClosedPiper J3 · Unadilla, GASep 1, 1964 · N42819 · Substantial damageNTSB UNK64X3717Accident · 1 fatalClosedTaylorcraft BC12D · Berlin, NJSep 1, 1964 · N43582 · DestroyedNTSB UNK64X3700Accident · No injuriesClosedGrumman G164 · Hickory Ridge, ARAug 31, 1964 · N10272 · Substantial damageNTSB FTW66D0131Accident · No injuriesClosedStinson 108-2 · Fabens, TXAug 31, 1964 · N9488K · DestroyedNTSB UNK64X3695Accident · Serious injuriesClosedLaister LK10A · Ren0, NVAug 30, 1964 · N53611 · DestroyedNTSB UNK64X3692Accident · 4 fatalClosedCessna 182 · Tulare, CAAug 30, 1964 · N5507B · DestroyedNTSB UNK64X3661Accident · No injuriesClosedBeech D18 · Springfield, OHAug 29, 1964 · N2047D · Substantial damageNTSB UNK64X3645Accident · Minor injuriesClosedCessna 170B · South Naknek, AKAug 28, 1964 · N3102B · DestroyedNTSB UNK64X3628Accident · No injuriesClosedBeechcraft M23 · Liberal, KSAug 27, 1964 · N2303Q · Substantial damageNTSB UNK64X3622Accident · Minor injuriesClosedBoeing A75 · Monroe, LAAug 27, 1964 · N121J · DestroyedNTSB UNK64X3620Accident · No injuriesClosedPiper PA18 · Buckner, MOAug 27, 1964 · N1206A · Substantial damageNTSB UNK64X3590Accident · Serious injuriesClosedNavion A · Dallas, TXAug 25, 1964 · N91718 · Substantial damageNTSB UNK64X3573Accident · Minor injuriesClosedPiper J3C · Somerset City, MEAug 24, 1964 · N25784 · Substantial damageNTSB UNK64X3564Accident · Minor injuriesClosedBeechcraft F35 · Haddam, KSAug 23, 1964 · N3868B · Substantial damage