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 SEA66F0072Accident · No injuriesClosedPiper PA-22 · Nampa, IDAug 8, 1965 · N6753B · Substantial damageNTSB NYC66D0172Accident · No injuriesClosedPiper PA-20S · Edinburg, NYAug 8, 1965 · N1541A · Substantial damageNTSB MKC66D0219Accident · No injuriesClosedRobinson Mere M · Davis City, IAAug 8, 1965 · N6827D · Substantial damageNTSB DEN66D0081Accident · No injuriesClosedTaylorcraft BC-12D · Orchard, COAug 8, 1965 · N43244 · Substantial damageNTSB MIA66F0094Accident · No injuriesClosedMooney M20A · Charlotte, NCAug 7, 1965 · N6022X · Substantial damageNTSB FTW66F0170Accident · No injuriesClosedGrumman G-164 · Lakeland, LAAug 7, 1965 · N463RF · Substantial damageNTSB ANC66D0049Accident · Minor injuriesClosedPiper PA-12 · Anchorage, AKAug 7, 1965 · N3992M · Substantial damageNTSB NYC66F0154Accident · Minor injuriesClosedPiper PA-28 · Lenox, MAAug 6, 1965 · N9029W · Substantial damageNTSB MKC66D0183Accident · No injuriesClosedPiper PA-12 · Pipestone, MNAug 6, 1965 · N4061M · Substantial damageNTSB CHI66F0175Accident · Serious injuriesClosedCessna 172 · Plymouth, MIAug 6, 1965 · N8449U · Substantial damageNTSB CHI66F0117Accident · Minor injuriesClosedStits Plaboy · Pontiac, MIAug 6, 1965 · N6383T · Substantial damageNTSB ANC65F0019Accident · No injuriesClosedPiper PA-12 · Tok, AKAug 6, 1965 · N3539M · Substantial damageNTSB SEA66F0108Accident · Minor injuriesClosedMooney M20C · Troutdale, ORAug 5, 1965 · N78942 · Substantial damageNTSB NYC66D0138Accident · No injuriesClosedSchweizer SGU-2 · Flatbrookville, NJAug 5, 1965 · N8609R · Substantial damageNTSB FTW66F0264Accident · No injuriesClosedBoeing A-75N1 · Houma, LAAug 5, 1965 · N1559M · Substantial damageNTSB DEN66D0069Accident · Minor injuriesClosedLuscombe 8E · Grants, NMAug 5, 1965 · N71113 · Substantial damageNTSB CHI66F0169Accident · No injuriesClosedAero Cmdr 520 · Hamilton, OHAug 4, 1965 · N111 · Substantial damageNTSB MKC66D0182Accident · No injuriesClosedRepublic RC-3 · Onamia, MNAug 3, 1965 · N6204K · Substantial damageNTSB MIA66F0118Accident · No injuriesClosedPiper PA-28 · Selma, ALAug 3, 1965 · N5880W · Substantial damageNTSB LAX66F0063Accident · No injuriesClosedBoeing B-75 · Lamont, CAAug 3, 1965 · N66711 · Substantial damageNTSB FTW66D0220Accident · Minor injuriesClosedCessna 150 · Wilson, OKAug 3, 1965 · N4163U · Substantial damageNTSB CHI66F0172Accident · No injuriesClosedPiper PA-22 · Ravenna, MIAug 3, 1965 · N181F · Substantial damageNTSB LAX66F0093Accident · No injuriesClosedAero Cmdr 680E · Reno, NVAug 2, 1965 · N9363R · Substantial damageNTSB FTW66F0218Accident · Minor injuriesClosedPiper PA-25 · Boydell, ARAug 2, 1965 · N7256Z · DestroyedNTSB FTW66F0189Accident · No injuriesClosedSnow S2C · Garwood, TXAug 2, 1965 · N1672S · Substantial damageNTSB FTW66D0240Accident · No injuriesClosedMooney M21 · Abilene, TXAug 2, 1965 · N78917 · Substantial damageNTSB NYC66F0101Accident · No injuriesClosedLuscombe 8A · Tupper Lake, NYAug 1, 1965 · N77861 · Substantial damageNTSB MIA66F0127Accident · No injuriesClosedPiper J3C · Indian Trail, NCAug 1, 1965 · N70889 · Substantial damageNTSB FTW66F0216Accident · Minor injuriesClosedCessna 120 · San Antonio, TXAug 1, 1965 · N90069 · Substantial damageNTSB DEN66F0071Accident · Minor injuriesClosedLuscombe 8E · Wendon, AZAug 1, 1965 · N2539K · Substantial damageNTSB LAX66F0048Accident · No injuriesClosedCessna 210 · Santa Ana, CAJul 30, 1965 · N7373E · Substantial damageNTSB CHI66F0139Accident · Serious injuriesClosedPiper PA-22 · Golden Pond, KYJul 30, 1965 · N9224D · Substantial damageNTSB FTW66A0010Accident · 6 fatalClosedPiper PA-23 · Tahlequah, OKJul 29, 1965 · N2025P · DestroyedNTSB MIA66A0016Accident · No injuriesClosedBell 47G · Panama City, FLJul 26, 1965 · N3628G · Substantial damageNTSB OAK66D0054Accident · No injuriesClosedCessna 170A · Tunnel Meadow, CAJul 25, 1965 · N9056A · Substantial damageNTSB NYC66D0143Accident · No injuriesClosedPiper PA22 · Warren, NHJul 25, 1965 · CFRNT · Substantial damageNTSB FTW66F0237Accident · Serious injuriesClosedPiper PA-25 · Frio, TXJul 25, 1965 · N6658Z · DestroyedNTSB CHI66F0140Accident · No injuriesClosedPiper PA-22 · Michigan City, INJul 25, 1965 · N4767Z · Substantial damageNTSB ANC66F0038Accident · No injuriesClosedPiper PA-18 · Mcgrath, AKJul 25, 1965 · N7545K · Substantial damageNTSB ANC66D0037Accident · No injuriesClosedCessna 170B · Takotna, AKJul 25, 1965 · N2961D · Substantial damageNTSB OAK66F0082Accident · Minor injuriesClosedCessna 140 · Dunsmuir, CAJul 24, 1965 · N76963 · Substantial damageNTSB MIA66D0069Accident · No injuriesClosedCessna 150D · Gillsburg, MSJul 24, 1965 · N4323U · Substantial damageNTSB LAX66F0094Accident · No injuriesClosedGlobe GD-1B · Sparks, NVJul 24, 1965 · N78252 · Substantial damageNTSB CHI66F0152Accident · Minor injuriesClosedStinson 108-2 · Beaver Dam, WIJul 24, 1965 · N286C · DestroyedNTSB NYC66A0012Accident · Serious injuriesClosedOnvair C V-440 · Montorsville, PAJul 23, 1965 · N815H · DestroyedNTSB FTW66F0166Accident · Serious injuriesClosedPiper PA-25 · Hough, OKJul 23, 1965 · N6739Z · Substantial damageNTSB DEN66D0049Accident · No injuriesClosedPiper PA-24 · Lucy, NMJul 23, 1965 · N8004P · Substantial damageNTSB SEA66F0095Accident · No injuriesClosedPiper PA-11 · Molt, MTJul 22, 1965 · N5066H · Substantial damageNTSB OAK66F0027Accident · Minor injuriesClosedFairchild M62 · Westley, CAJul 22, 1965 · N68444 · Substantial damageNTSB MKC66F0143Accident · No injuriesClosedPiper PA-28 · Kansas City, KSJul 22, 1965 · N6110W · Substantial damage