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 LAX68D0327Accident · No injuriesClosedPiper PA-24 · San Brnadno, CAJan 21, 1968 · N6445P · Substantial damageNTSB NYC68D0356Accident · No injuriesClosedCessna 140 · Milo, MEJan 20, 1968 · N89261 · Substantial damageNTSB DEN68D0216Accident · No injuriesClosedCessna 150 · Center, COJan 20, 1968 · N6420F · DestroyedNTSB LAX68A0070Accident · 1 fatalClosedPiper PA-32 · Studio City, CAJan 19, 1968 · N4002R · DestroyedNTSB DEN68D0196Accident · Minor injuriesClosedMooney M20A · Shiprock, NMJan 18, 1968 · N8320E · Substantial damageNTSB CHI68F0594Accident · No injuriesClosedPiper PA-24 · West Bend, WIJan 18, 1968 · N8042P · Substantial damageNTSB LAX68A0067Accident · Serious injuriesClosedHiller UH12L4 · Cucamonga, CAJan 15, 1968 · N90483 · Substantial damageNTSB LAX68F0318Accident · No injuriesClosedBeech C-45H · Palm Sprgs, CAJan 14, 1968 · N9818Z · Substantial damageNTSB LAX68D0284Accident · No injuriesClosedBeech A23 · Julian, CAJan 14, 1968 · N8794M · DestroyedNTSB OAK68D0301Accident · No injuriesClosedCessna 150 · Marysville, CAJan 12, 1968 · N6680F · Substantial damageNTSB DIA68D0144Accident · No injuriesClosedPiper PA-28 · Lexington, VAJan 12, 1968 · N4665R · Substantial damageNTSB MKC68F0467Accident · No injuriesClosedPiper PA-28 · Anoka, MNJan 11, 1968 · N508UM · Substantial damageNTSB NYC68F0341Accident · No injuriesClosedAlon A-2 · Hyannis, MAJan 9, 1968 · N6570Q · Substantial damageNTSB DIA68D0145Accident · No injuriesClosedPiper PA-22 · Asheville, NCJan 9, 1968 · N2102A · Substantial damageNTSB LAX68A0065Accident · 3 fatalClosedBeech E18S · San Diego, CAJan 8, 1968 · N82B · DestroyedNTSB CHI68F0538Accident · No injuriesClosedBeech G35 · Newberry, MIJan 8, 1968 · N4574D · Substantial damageNTSB DEN68D0193Accident · No injuriesClosedNavion A · Trout Creek, UTJan 7, 1968 · N8549H · Substantial damageNTSB SEA68F0245Accident · No injuriesClosedCessna A188 · Central Pont, ORJan 6, 1968 · N9734V · DestroyedNTSB LAX68F0359Accident · Minor injuriesClosedFairchild 24W9 · Hntngtn Bch, CAJan 6, 1968 · N49321 · Substantial damageNTSB LAX68F0252Accident · No injuriesClosedPiper PA-23 · Van Nuys, CAJan 5, 1968 · N1276P · Substantial damageNTSB MIA68F0393Accident · No injuriesClosedSwearingen SA-26T · Miami, FLJan 2, 1968 · N733H · Substantial damageNTSB LAX68D0274Accident · Minor injuriesClosedPiper PA-23 · Bullhead Cty, AZJan 1, 1968 · N594P · DestroyedNTSB DEN68F0187Accident · No injuriesClosedTaylorcraft BC12-D · Albuquerque, NMJan 1, 1968 · N95810 · Substantial damageNTSB MKC68F0441Accident · No injuriesClosedPiper PA-22 · Edwards, MODec 31, 1967 · N640A · Substantial damageNTSB FTW68F0411Accident · No injuriesClosedBeech A23 · Tyler, TXDec 29, 1967 · N6939Q · Substantial damageNTSB DEN68F0189Accident · No injuriesClosedBeech D18S · Albuquerque, NMDec 29, 1967 · N260R · Substantial damageNTSB DEN68D0186Accident · No injuriesClosedCessna 320C · Farmington, NMDec 29, 1967 · N3024T · Substantial damageNTSB OAK68F0297Accident · Minor injuriesClosedCessna 170B · Petaluma, CADec 28, 1967 · N2590C · DestroyedNTSB NYC68A0083Accident · No injuriesClosedCessna 182 · Oriskany, NYDec 27, 1967 · N2687R · Substantial damageNTSB MIA68F0361Accident · No injuriesClosedCessna 310D · Vero Beach, FLDec 27, 1967 · N6959T · Substantial damageNTSB CHI68F0516Accident · No injuriesClosedCessna 182 · Adrian, MIDec 27, 1967 · N3714U · Substantial damageNTSB MIA68D0379Accident · Minor injuriesClosedLuscombe 8A · Grand Turk Is.Bwi, TokelauDec 26, 1967 · N1597K · DestroyedNTSB CHI68D0523Accident · No injuriesClosedCessna 140 · Fitchburg, WIDec 24, 1967 · N1914N · Substantial damageNTSB LAX68F0264Accident · No injuriesClosedBeech E35 · Calipatria, CADec 23, 1967 · N7390B · Substantial damageNTSB DIA68D0141Accident · No injuriesClosedLuscombe 8A · Melfa, VADec 23, 1967 · N72039 · Substantial damageNTSB CHI68A0056Accident · 3 fatalClosedMooney M20F · Knox, INDec 22, 1967 · N2779W · DestroyedNTSB DEN68F0172Accident · No injuriesClosedPiper PA-24 · Littleton, CODec 21, 1967 · N5133P · Substantial damageNTSB DEN68A0027Accident · 2 fatalClosedDouglas DC-3C · Denver, CODec 21, 1967 · N65276 · DestroyedNTSB NYC68A0081Accident · No injuriesClosedBeech 65-B80 · Beverly, MADec 20, 1967 · N207E · Substantial damageNTSB MKC68F0472Accident · No injuriesClosedCessna 175 · Fenton, MODec 19, 1967 · N7593M · Substantial damageNTSB NYC68F0320Accident · No injuriesClosedCessna 337 · East Berlin, PADec 18, 1967 · N5355S · Substantial damageNTSB FTW68F0400Accident · No injuriesClosedCessna 15OF · Alexandria, LADec 18, 1967 · N8697S · Substantial damageNTSB FTW68F0399Accident · Minor injuriesClosedCessna 210C · Gatesville, TXDec 18, 1967 · N3928Y · Substantial damageNTSB MIA68D0349Accident · No injuriesClosedCessna 150 · Miami, FLDec 17, 1967 · N4218U · Substantial damageNTSB OAK68D0316Accident · No injuriesClosedBeech 35 · Milford, CADec 16, 1967 · N3702N · Substantial damageNTSB CHI68F0500Accident · No injuriesClosedBeech C-45 · Waterford, MIDec 16, 1967 · N9800H · Substantial damageNTSB OAK68D0247Accident · No injuriesClosedGrumman G-164 · Merced, CADec 14, 1967 · N703Y · Substantial damageNTSB MIA68D0322Accident · No injuriesClosedCessna 150 · Hollywood, FLDec 14, 1967 · N8851G · Substantial damageNTSB MIA68F0377Accident · No injuriesClosedCessna 170A · Wadley, GADec 13, 1967 · N81Z · Substantial damageNTSB MIA68F0363Accident · No injuriesClosedCessna 150F · Bartow, FLDec 13, 1967 · N8758G · Substantial damage