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 NYC66F0125Accident · 1 fatalClosedPiper PA-28 · Spring Valley, NYJun 29, 1965 · N8131W · Substantial damageNTSB MKC66F0080Accident · Minor injuriesClosedAeronca 7AC · Amoret, MOJun 28, 1965 · N83177 · Substantial damageNTSB MKC66D0054Accident · No injuriesClosedPiper PA-18A · Sutherland, NEJun 28, 1965 · N1894A · Substantial damageNTSB UNK65X1508Accident · Minor injuriesClosedPiper PA-18S · Greenhill Lk, CanadaJun 27, 1965 · N7201K · DestroyedNTSB SEA66F0093Accident · Serious injuriesClosedPhillips CT-2 · Vancouver, WAJun 27, 1965 · N19989 · Substantial damageNTSB MKC66F0097Accident · Serious injuriesClosedCessna 172C · Ozark, MOJun 26, 1965 · N1626Y · Substantial damageNTSB FTW66D0067Accident · No injuriesClosedBeechcraft 23 · Wayside, TXJun 26, 1965 · N3532R · Substantial damageNTSB DEN66D0016Accident · No injuriesClosedMooney M20A · Vernal, UTJun 26, 1965 · N6581B · Substantial damageNTSB CHI66D0043Accident · Minor injuriesClosedCessna 182 · Pigeon, MIJun 26, 1965 · N6115B · Substantial damageNTSB MIA66D0056Accident · No injuriesClosedStinson 10 · Milton, FLJun 25, 1965 · N26431 · DestroyedNTSB CHI66F0001Accident · No injuriesClosedPiper PA-24 · Louisville, KYJun 24, 1965 · N6780P · Substantial damageNTSB ANC66F0005Accident · No injuriesClosedAmerican 100B · Dry Bay, AKJun 24, 1965 · N709Y · Substantial damageNTSB MKC66F0044Accident · Minor injuriesClosedLuscombe 8E · Colwich, KSJun 23, 1965 · N1851K · DestroyedNTSB FTW66F0235Accident · No injuriesClosedPiper PA-25 · Dimmitt, TXJun 23, 1965 · N6734Z · DestroyedNTSB FTW66F0114Accident · No injuriesClosedPiper PA-25 · Batesville, TXJun 23, 1965 · N6662Z · Substantial damageNTSB CHI66D0021Accident · No injuriesClosedPiper PA-22 · Strongsville, OHJun 23, 1965 · N5722Z · Substantial damageNTSB NYC66F0097Accident · No injuriesClosedMoonraker 100 · Gaithersburg, MDJun 22, 1965 · N8669E · Substantial damageNTSB FTW66F0106Accident · Minor injuriesClosedCessna 150C · Gilmer, TXJun 21, 1965 · N2002Z · Substantial damageNTSB FTW66F0053Accident · Minor injuriesClosedCessna 172 · Troy, LAJun 21, 1965 · N2758U · DestroyedNTSB NYC66F0026Accident · No injuriesClosedPiper PA-28 · Bolton, NYJun 20, 1965 · N7670W · Substantial damageNTSB MKC66F0187Accident · No injuriesClosedBeechcraft 23 · Fergus Falls, MNJun 20, 1965 · N8739M · Substantial damageNTSB MIA66F0134Accident · Minor injuriesClosedPiper PA-28 · Quincy, FLJun 20, 1965 · N7723W · DestroyedNTSB DEN66D0024Accident · No injuriesClosedTaylorcraft BC-65 · Gunnison, COJun 20, 1965 · N36205 · Substantial damageNTSB CHI65A0106Accident · No injuriesClosedHughes 269B · Gile, WIJun 20, 1965 · N9355F · Substantial damageNTSB CHI65A0105Accident · 2 fatalClosedFairchild PT-19 · St.Clairsville, OHJun 20, 1965 · N47495 · DestroyedNTSB ANC65A0055Accident · 1 fatalClosedErcoupe 415C · Palmer, AKJun 20, 1965 · N94149 · DestroyedNTSB SEA66F0021Accident · No injuriesClosedCessna 120 · Brewster, WAJun 19, 1965 · N2792N · Substantial damageNTSB NYC65A0150Accident · 2 fatalClosedTaylorcraft DCO-65 · Westerly, RIJun 19, 1965 · N58041 · DestroyedNTSB MIA66F0145Accident · Serious injuriesClosedRyan ST3KR · Atlanta, GAJun 19, 1965 · N48238 · DestroyedNTSB FTW66F0103Accident · No injuriesClosedStinson L-5G · Norman, OKJun 19, 1965 · N63854 · Substantial damageNTSB ANC66D0018Accident · Serious injuriesClosedPiper J3 · Pederson Pt, AKJun 19, 1965 · N4002H · DestroyedNTSB OAK66F0045Accident · Minor injuriesClosedLuscombe 8A · Tracy, CAJun 18, 1965 · N23043 · Substantial damageNTSB DEN66D0025Accident · No injuriesClosedCessna 140 · Clovis, NMJun 18, 1965 · N89428 · Substantial damageNTSB MKC66F0073Accident · No injuriesClosedPiper PA-23 · Des Moines, IAJun 17, 1965 · N3348P · Substantial damageNTSB FTW66F0055Accident · 1 fatalClosedLuscombe 8A · Smithfield, TXJun 15, 1965 · N77847 · DestroyedNTSB DEN66D0002Accident · No injuriesClosedPiper PA-24 · Grand Canyon, AZJun 15, 1965 · N5728P · Substantial damageNTSB MKC66D0140Accident · No injuriesClosedPiper PA-18A · Orrick, MOJun 14, 1965 · N9481D · Substantial damageNTSB OAK66F0004Accident · No injuriesClosedLuscombe 8A · Modesto, CAJun 13, 1965 · N1141B · DestroyedNTSB NYC66D0017Accident · No injuriesClosedCessna 172 · Greenbank, WVJun 13, 1965 · N7165A · Substantial damageNTSB MKC66D0025Accident · No injuriesClosedPiper PA-15 · Riceville, IAJun 13, 1965 · N4189H · DestroyedNTSB LAX66F0001Accident · Minor injuriesClosedBeechcraft D-18S · Minden, NVJun 13, 1965 · N9506Z · DestroyedNTSB CHI66F0061Accident · No injuriesClosedCessna 140 · East Moline, ILJun 12, 1965 · N1891V · Substantial damageNTSB CHI66F0024Accident · No injuriesClosedPiper PA-28B · Murray, KYJun 12, 1965 · N5633W · Substantial damageNTSB ANC66D0017Accident · No injuriesClosedAero Cmdr 500B · Kodiak, AKJun 12, 1965 · N6392U · Substantial damageNTSB SEA66D0001Accident · No injuriesClosedRawdon T1 · Oakesdale, WAJun 11, 1965 · N6804D · Substantial damageNTSB NYC66F0003Accident · Serious injuriesClosedLuscombe 8A · Mitchellville, MDJun 11, 1965 · N77Q · Substantial damageNTSB NYC65A0147Accident · No injuriesClosedBell 47G2 · Laurel, MDJun 11, 1965 · N6758D · Substantial damageNTSB ANC66D0011Accident · No injuriesClosedPiper PA-22 · Mankomen Lke, AKJun 11, 1965 · N2769P · Substantial damageNTSB OAK66F0018Accident · Serious injuriesClosedPiper PA-28 · Oakland, CAJun 10, 1965 · N6350W · Substantial damageNTSB OAK65F0079Accident · No injuriesClosedBoeing D-75 · Maxwell, CAJun 10, 1965 · N56747 · Substantial damage