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 DEN67F0062Accident · No injuriesClosedCessna 188 · Center, COJun 16, 1966 · N5507S · Substantial damageNTSB MIA67F0006Accident · Minor injuriesClosedPiper PA12 · China Grove, NCJun 15, 1966 · N3656M · Substantial damageNTSB MKC67F0019Accident · No injuriesClosedPiper PA-18 · Carrington, NDJun 14, 1966 · N6187D · Substantial damageNTSB DEN67D0063Accident · Minor injuriesClosedPiper PA-22 · Conifer, COJun 14, 1966 · N5633Z · Substantial damageNTSB MKC66D0586Accident · No injuriesClosedBoeing A75 · Berryton, KSJun 13, 1966 · N73357 · DestroyedNTSB NYC66A0148Accident · 2 fatalClosedPiper PA-24 · Shirley, NYJun 12, 1966 · N8622P · DestroyedNTSB NYC66A0147Accident · 1 fatalClosedMooney M-18C · Slate Run, PAJun 12, 1966 · N4170 · DestroyedNTSB MIA67F0095Accident · Serious injuriesClosedStinson 108-2 · Atlanta, GAJun 12, 1966 · N40155 · DestroyedNTSB FTW67F0013Accident · No injuriesClosedCessna 172 · Hackberry, LAJun 12, 1966 · N3719S · Substantial damageNTSB CHI67F0032Accident · No injuriesClosedAeronca 11AC · Kalamo, MIJun 12, 1966 · N85825 · Substantial damageNTSB OAK66A0056Accident · 2 fatalClosedCessna 172 · Seigler Sprgs, CAJun 11, 1966 · N7204A · DestroyedNTSB DEN66D0309Accident · No injuriesClosedCessna 182 · Santa Rosa, NMJun 10, 1966 · N2638G · Substantial damageNTSB MKC67D0013Accident · No injuriesClosedPiper PA-22 · Manson, IAJun 9, 1966 · N8516C · Substantial damageNTSB MIA66D0613Accident · No injuriesClosedAeronca 7ECA · Manati, PRJun 9, 1966 · N2531F · Substantial damageNTSB LAX66A0097Accident · Minor injuriesClosedBell 47G3B1 · Glendora, CAJun 6, 1966 · N3079G · Substantial damageNTSB FTW67D0034Accident · Minor injuriesClosedBoeing E75N1 · Nome, TXJun 6, 1966 · N4566N · Substantial damageNTSB FTW67D0012Accident · No injuriesClosedPiper PA-18 · Honey Grove, TXJun 6, 1966 · N4372A · Substantial damageNTSB CHI67F0017Accident · Serious injuriesClosedPiper PA-24 · Chetek, WIJun 5, 1966 · N7709P · DestroyedNTSB MIA67F0158Accident · Serious injuriesClosedCessna 172 · Palatka, FLJun 4, 1966 · N4139F · Substantial damageNTSB LAX66F0380Accident · Serious injuriesClosedCessna 172G · Fullerton, CAJun 4, 1966 · N3768L · Substantial damageNTSB CHI67F0027Accident · No injuriesClosedMooney M20 · Jackson, MIJun 4, 1966 · N5220B · Substantial damageNTSB CHI67F0013Accident · Serious injuriesClosedBeech B35 · Indianapolis, INJun 4, 1966 · N5201C · Substantial damageNTSB NYC67F0067Accident · No injuriesClosedAmer Eagle 231 · East Amherst, NYJun 3, 1966 · N487V · Substantial damageNTSB MIA66F0595Accident · Minor injuriesClosedMooney MK20C · Pascagoula, MSJun 3, 1966 · N5555Q · DestroyedNTSB CHI66F0793Accident · No injuriesClosedPiper PA-23 · Madison, INJun 3, 1966 · N241RV · Substantial damageNTSB NYC66F0678Accident · No injuriesClosedCessna 205 · E.Stroudsburg, PAJun 1, 1966 · N8211Z · Substantial damageNTSB NYC66A0138Accident · 1 fatalClosedCessna 150F · Fairfield, NJMay 31, 1966 · N8782S · DestroyedNTSB MIA66F0562Accident · Minor injuriesClosedPiper PA-22 · Alexis, NCMay 30, 1966 · N6593D · Substantial damageNTSB DEN66D0355Accident · No injuriesClosedCessna 150 · Douglas, AZMay 30, 1966 · N4533U · Substantial damageNTSB SEA67F0060Accident · Serious injuriesClosedChampion 7GCBC · Prineville, ORMay 29, 1966 · N9651S · DestroyedNTSB MKC67D0045Accident · No injuriesClosedMooney M20C · Wellington, KSMay 29, 1966 · N6460U · Substantial damageNTSB LAX66D0368Accident · No injuriesClosedCessna 172 · Apple Valley, CAMay 29, 1966 · N8863U · DestroyedNTSB FTW66A0110Accident · 1 fatalClosedBeech 35 · Pt Mansfield, TXMay 29, 1966 · N2931V · DestroyedNTSB CHI66F0786Accident · Minor injuriesClosedCessna 182 · Antioch, ILMay 29, 1966 · N6361A · Substantial damageNTSB SEA67D0011Accident · Minor injuriesClosedCessna 180A · Albion, IDMay 28, 1966 · N7770A · DestroyedNTSB OAK66F0285Accident · Serious injuriesClosedPiper PA-24 · Elk Grove, CAMay 28, 1966 · N6189P · DestroyedNTSB CHI67D0356Accident · Minor injuriesClosedPiper PA-20 · No.Jackson, OHMay 28, 1966 · N7002K · Substantial damageNTSB CHI66F0767Accident · No injuriesClosedPiper PA-22 · Napoleon, MIMay 28, 1966 · N2634P · Substantial damageNTSB ANC67D0003Accident · No injuriesClosedAeronca 7GC · Anchorage, AKMay 27, 1966 · N8501E · DestroyedNTSB MIA66F0567Accident · No injuriesClosedBeech A45 · Ocean Sprngs, MSMay 26, 1966 · N8239E · Substantial damageNTSB FTW66F0681Accident · No injuriesClosedPiper PA-18 · Stuttgart, ARMay 26, 1966 · N1028A · Substantial damageNTSB MKC67F0005Accident · Minor injuriesClosedPiper PA-28 · Wichita, KSMay 24, 1966 · N6410W · DestroyedNTSB LAX66A0091Accident · No injuriesClosedGrumman G-21 · Catalina Is, CAMay 24, 1966 · N323 · Substantial damageNTSB DEN66F0343Accident · Serious injuriesClosedPiper PA-28 · Albuquerque, NMMay 24, 1966 · N6199W · DestroyedNTSB CHI66A0091Accident · 1 fatalClosedBeech S35 · Monticello, ILMay 24, 1966 · N5842S · DestroyedNTSB UNK66X0142Accident · Minor injuriesClosedCessna 180 · Prince Albert, CanadaMay 23, 1966 · N2832A · DestroyedNTSB ANC66A0045Accident · 2 fatalClosedCessna 172F · Galena, AKMay 22, 1966 · N8809U · DestroyedNTSB SEA66D0288Accident · No injuriesClosedBellanca 14-13 · Boulder, MTMay 21, 1966 · N86916 · Substantial damageNTSB OAK66F0307Accident · 1 fatalClosedBeech SNB-5 · Lihue, HIMay 21, 1966 · N91296 · DestroyedNTSB SEA67D0061Accident · No injuriesClosedPiper PA-22 · Eugene, ORMay 20, 1966 · N5210Z · Substantial damage