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 UNK64X2641Accident · No injuriesClosedPiper PA22 · Faribault, MNJul 4, 1964 · N7053B · Substantial damageNTSB UNK64X2638Accident · No injuriesClosedAeronca 7AC · Moore Haven, FLJul 4, 1964 · N83708 · Substantial damageNTSB UNK64X2618Accident · Minor injuriesClosedPiper J3 · Deer Park, NYJul 3, 1964 · N32643 · DestroyedNTSB UNK64X2612Accident · Minor injuriesClosedTaylorcraft BC12D · Gaston, ORJul 3, 1964 · N96033 · DestroyedNTSB UNK64X2582Accident · Minor injuriesClosedBenson B8M · Valley City, NDJun 30, 1964 · N1065Z · DestroyedNTSB UNK64X2556Accident · No injuriesClosedAero Cmdr 520 · Pembroke, CanadaJun 29, 1964 · N501K · Substantial damageNTSB UNK64X2555Accident · No injuriesClosedChampion 7AC · Excelsior Spgs, MOJun 28, 1964 · N85478 · Substantial damageNTSB UNK64X2553Accident · No injuriesClosedPiper PA18 · Atlanta, GAJun 28, 1964 · N723C · Substantial damageNTSB UNK64X2549Accident · No injuriesClosedLuscombe 8A · Cushing, WIJun 28, 1964 · N2943K · Substantial damageNTSB UNK64X2546Accident · No injuriesClosedPiper PA12 · Mound, MNJun 28, 1964 · N3977M · Substantial damageNTSB UNK64X2543Accident · Minor injuriesClosedStinson 10 · Havasu City, AZJun 28, 1964 · N27751 · DestroyedNTSB UNK64X2541Accident · No injuriesClosedPiper PA16 · Boulevard, CAJun 28, 1964 · N5858H · Substantial damageNTSB UNK64X2529Accident · Serious injuriesClosedCessna 182B · Wilmington, NCJun 27, 1964 · N2775G · DestroyedNTSB UNK64X2511Accident · No injuriesClosedCessna 172B · Menachemeadow, CAJun 27, 1964 · N7780X · Substantial damageNTSB UNK64X2509Accident · No injuriesClosedBoeing A75 · Arvin, CAJun 27, 1964 · N62659 · Substantial damageNTSB UNK64X2490Accident · No injuriesClosedStearman YIS · Dunlap, INJun 26, 1964 · N15521 · Substantial damageNTSB UNK64X2473Accident · Serious injuriesClosedPiper J3 · Harlingen, TXJun 24, 1964 · N4495M · DestroyedNTSB UNK64X2469Accident · No injuriesClosedPiper J3C · Fairwell Lke, AKJun 24, 1964 · N70312 · Substantial damageNTSB UNK64X2465Accident · Minor injuriesClosedCessna 172 · Freemason Is, LAJun 24, 1964 · N7368T · DestroyedNTSB UNK64X2429Accident · Serious injuriesClosedStinson L-5 · Newcastle, VAJun 21, 1964 · N55592 · DestroyedNTSB UNK64X2425Accident · No injuriesClosedCessna 195 · Huntsville, ARJun 21, 1964 · N2162C · Substantial damageNTSB UNK64X2418Accident · Minor injuriesClosedCessna 172 · Ansonia, OHJun 21, 1964 · N5776A · DestroyedNTSB UNK64X2413Accident · No injuriesClosedPiper PA22 · Ft Meade, MDJun 21, 1964 · N5780Z · Substantial damageNTSB UNK64X2407Accident · 2 fatalClosedBeech SNB-5 · Ft Lauderdale, FLJun 20, 1964 · N90280 · DestroyedNTSB UNK64X2398Accident · No injuriesClosedPiper PA18 · Sleepy Eye, MNJun 20, 1964 · N2421A · Substantial damageNTSB UNK64X2377Accident · No injuriesClosedMooney MK20 · Wolfe City, TXJun 19, 1964 · N875B · Substantial damageNTSB UNK64X2371Accident · No injuriesClosedCessna 140 · Santanta, KSJun 19, 1964 · N2367V · DestroyedNTSB UNK64X2370Accident · Minor injuriesClosedLuscombe 8A · Lancaster, CAJun 19, 1964 · N23031 · Substantial damageNTSB UNK64X2356Accident · No injuriesClosedPiper PA23 · Lake Tahoe, CAJun 18, 1964 · N3068P · Substantial damageNTSB UNK64X2353Accident · Minor injuriesClosedPiper PA22 · New Alexander, PAJun 18, 1964 · N7559D · Substantial damageNTSB MKC66D0051Accident · No injuriesClosedCessna 180F · Petersburg, NDJun 18, 1964 · N2134Z · Substantial damageNTSB UNK64X2346Accident · Serious injuriesClosedCessna 140 · Ft Lauderdale, FLJun 17, 1964 · N90198 · DestroyedNTSB UNK64X2344Accident · 3 fatalClosedBeechcraft A35 · Rawlins, WYJun 17, 1964 · N8552A · DestroyedNTSB UNK64X2329Accident · Minor injuriesClosedGlobe Swift GC1B · Texarkana, ARJun 17, 1964 · N78285 · DestroyedNTSB UNK64X2298Accident · No injuriesClosedPiper PA25 · Spearman, TXJun 15, 1964 · N6520Z · Substantial damageNTSB UNK64X2294Accident · No injuriesClosedHiller 12E · Jacob Lake, AZJun 14, 1964 · N5341V · Substantial damageNTSB UNK64X2287Accident · No injuriesClosedPiper PA28 · Burwell, NEJun 14, 1964 · N5152W · Substantial damageNTSB UNK64X2278Accident · No injuriesClosedPiper PA22 · Ridgeland, SCJun 14, 1964 · N1948P · Substantial damageNTSB UNK64X2270Accident · 1 fatalClosedMooney M18C · Grants, NMJun 13, 1964 · N4067 · DestroyedNTSB UNK64X2257Accident · Minor injuriesClosedCessna 150 · Akkansas Cty, KSJun 13, 1964 · N7252K · DestroyedNTSB UNK64X2249Accident · No injuriesClosedBeechcraft 35 · Piedmont, OKJun 12, 1964 · N3388V · Substantial damageNTSB UNK64X2225Accident · Minor injuriesClosedTribby 1 · San Antonio, TXJun 11, 1964 · N8083E · Substantial damageNTSB UNK64X2224Accident · No injuriesClosedTaylorcraft BL1265 · Rawlings, WYJun 11, 1964 · N36064 · DestroyedNTSB UNK64X2218Accident · No injuriesClosedPiper PA22 · Rockville, MDJun 10, 1964 · N3323Z · Substantial damageNTSB UNK64X2216Accident · Minor injuriesClosedTaylorcraft BC-12D · Slidell, LAJun 10, 1964 · N96003 · Substantial damageNTSB UNK64X2215Accident · No injuriesClosedPiper PA18 · Karinen, SDJun 10, 1964 · N4053Z · Substantial damageNTSB UNK64X2210Accident · No injuriesClosedBell 47G3 · Yack Flats, MTJun 9, 1964 · N8400E · Substantial damageNTSB UNK64X2202Accident · No injuriesClosedPiper PA24 · Dimmitt, TXJun 9, 1964 · N5656P · Substantial damageNTSB UNK64X2198Accident · No injuriesClosedMooney M20E · Richton Park, ILJun 9, 1964 · N1316W · Substantial damageNTSB UNK64X2195Accident · 1 fatalClosedAero Cmdr 680F · Del Cabo Baja, MexicoJun 9, 1964 · N264X · Destroyed