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 FTW70F0089Accident · Minor injuriesClosedCessna 177 · Matagorda Is., TXJul 4, 1969 · N3355T · Substantial damageNTSB MKC70D0093Accident · Serious injuriesClosedCessna 177 · Stanley, KSJul 3, 1969 · N3494T · Substantial damageNTSB MIA70F0079Accident · Minor injuriesClosedHummel J T · St.Petersburg, FLJul 3, 1969 · N6434 · Substantial damageNTSB FTW70D0098Accident · No injuriesClosedPiper PA-25 · Fisher, ARJul 3, 1969 · N4995Y · DestroyedNTSB CHI70F0063Accident · No injuriesClosedNavion A · Hartford City, INJul 3, 1969 · N91512 · Substantial damageNTSB SEA70F0031Accident · Minor injuriesClosedTaylorcraft BC12-D · Kalispell, MTJul 1, 1969 · N95581 · Substantial damageNTSB OAK70D0071Accident · No injuriesClosedSwift GC-1B · Sheltr Cove, CAJun 29, 1969 · N78209 · Substantial damageNTSB MKC70D0047Accident · No injuriesClosedLuscombe 8E · Dempster, SDJun 29, 1969 · N1820K · Substantial damageNTSB MIA70F0029Accident · No injuriesClosedPiper PA-28 · Birmingham, ALJun 29, 1969 · N5097W · Substantial damageNTSB DEN70F0070Accident · No injuriesClosedAeronca 7BCM · Clifton, COJun 29, 1969 · N2704E · Substantial damageNTSB CHI70F0084Accident · Minor injuriesClosedN.American AT-6 · Ann Arbor, MIJun 29, 1969 · N7987C · DestroyedNTSB CHI70D0064Accident · Minor injuriesClosedPiper PA-28 · Alma, MIJun 29, 1969 · N3979K · Substantial damageNTSB ANC70D0024Accident · No injuriesClosedAeronca 11AC · Soldatna, AKJun 29, 1969 · N4676C · Substantial damageNTSB MKC70D0211Accident · Serious injuriesClosedPiper PA-14 · Pelican Rpds, MNJun 28, 1969 · N4290H · DestroyedNTSB MKC70D0088Accident · No injuriesClosedAeronca S15AC · Aitkin, MNJun 28, 1969 · N1417H · Substantial damageNTSB MIA70F0209Accident · No injuriesClosedCessna 150 · Dahlonega, GAJun 28, 1969 · N60344 · Substantial damageNTSB MKC70F0059Accident · Serious injuriesClosedBoeing A75 · Argusville, NDJun 27, 1969 · N63880 · Substantial damageNTSB ANC70F0011Accident · Minor injuriesClosedN.American TB-25N · Fairbanks, AKJun 27, 1969 · N90887 · Substantial damageNTSB CHI70F0201Accident · Serious injuriesClosedPiper J3L-65 · North Canton, OHJun 26, 1969 · N38827 · Substantial damageNTSB CHI70F0062Accident · No injuriesClosedPiper PA-28 · Cincinnati, OHJun 26, 1969 · N6368J · Substantial damageNTSB CHI70F0061Accident · Minor injuriesClosedCessna 172H · Union, OHJun 26, 1969 · N2861L · Substantial damageNTSB CHI70F0066Accident · No injuriesClosedCessna 170 · Monroe, MIJun 25, 1969 · N9160A · Substantial damageNTSB SEA69A0062Accident · 3 fatalClosedConvair 880 · Moses Lake, WAJun 24, 1969 · JA-8028 · DestroyedNTSB MIA70D0005Accident · No injuriesClosedGrumman 164 · Dayton, ALJun 24, 1969 · N10289 · DestroyedNTSB DEN70F0024Accident · Serious injuriesClosedCessna 210 · Roy, UTJun 23, 1969 · N8158Z · Substantial damageNTSB DCA70A0002Accident · 4 fatalClosedDouglas C-54D · Miami, FLJun 23, 1969 · HI-168 · DestroyedNTSB MIA70F0035Accident · Minor injuriesClosedFairchild M-62A · W Palm Beach, FLJun 22, 1969 · N58140 · Substantial damageNTSB CHI69A0102Accident · 3 fatalClosedPiper PA-30 · Inkster, MIJun 22, 1969 · N7906Y · DestroyedNTSB OAK70F0015Accident · No injuriesClosedBell 206A · Tonopah, NVJun 21, 1969 · N1465W · Substantial damageNTSB SEA69A0061Accident · 2 fatalClosedBeech 99 · Pasco, WAJun 20, 1969 · N2550A · DestroyedNTSB MIA70D0031Accident · Minor injuriesClosedCessna 336 · Frsh Crek, Burkina FasoJun 20, 1969 · N1715Z · DestroyedNTSB FTW70D0061Accident · Minor injuriesClosedBeech D18S · Oklahoma Cty, OKJun 20, 1969 · N68B · Substantial damageNTSB CHI69A0101Accident · 1 fatalClosedLuscombe 8A · Rockford, ILJun 20, 1969 · N71362 · DestroyedNTSB FTW70F0056Accident · No injuriesClosedPiper PA-25 · Texarkana, ARJun 19, 1969 · N4559Y · DestroyedNTSB DEN70F0011Accident · No injuriesClosedPiper PA-24 · Albuquerque, NMJun 19, 1969 · N6311P · Substantial damageNTSB DEN70D0004Accident · No injuriesClosedPiper PA-18 · Carrizozo, NMJun 19, 1969 · N1701A · Substantial damageNTSB FTW70F0055Accident · Serious injuriesClosedBoeing E75 · Ganado, TXJun 17, 1969 · N5328N · Substantial damageNTSB CHI70F0111Accident · 1 fatalClosedPiper PA-28 · Grayslake, ILJun 17, 1969 · N6788W · DestroyedNTSB MKC70D0051Accident · No injuriesClosedPiper PA-22 · Rantoul, KSJun 16, 1969 · N8035D · Substantial damageNTSB DIA70D0004Accident · No injuriesClosedCessna 150 · Front Royal, VAJun 16, 1969 · N4099J · Substantial damageNTSB FTW69F0009Accident · No injuriesClosedCessna 150 · Keller, TXJun 15, 1969 · N5516E · Substantial damageNTSB NYC70D0028Accident · No injuriesClosedCessna 150 · Westboro, MAJun 14, 1969 · N3208X · Substantial damageNTSB ANC70F0009Accident · No injuriesClosedBellanca 17-30 · Minchumina, AKJun 13, 1969 · N4884V · Substantial damageNTSB MKC70D0057Accident · No injuriesClosedCessna 172I · Ely, MNJun 12, 1969 · N8425L · Substantial damageNTSB CHI70F0069Accident · No injuriesClosedPlaymate II · Angola, INJun 12, 1969 · N1093Z · Substantial damageNTSB NYC70D0009Accident · No injuriesClosedCessna 150F · Caribou, MEJun 11, 1969 · N8208F · Substantial damageNTSB MIA70F0084Accident · No injuriesClosedAero Comdr B-1A · Quincy, FLJun 10, 1969 · N7241V · Substantial damageNTSB FTW70D0052Accident · No injuriesClosedBoeing E75 · Winnie, TXJun 10, 1969 · N4566N · Substantial damageNTSB MIA69I0088Incident · No injuriesClosedDouglas DC-9 · Chattanooga, TNJun 9, 1969 · N8946E · No damageNTSB LAX70F0039Accident · Minor injuriesClosedCessna TU206 · Santa Paula, CAJun 9, 1969 · N3995G · Substantial damage