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 SEA69F0126Accident · Minor injuriesClosedCessna 172G · Canyon City, ORSep 7, 1968 · N5784R · DestroyedNTSB SEA69D0135Accident · No injuriesClosedCessna 180 · Miles City, MTSep 7, 1968 · N9907V · Substantial damageNTSB NYC69D0183Accident · No injuriesClosedStinson 108-3 · Dublin, PASep 7, 1968 · N6866M · Substantial damageNTSB LAX69D0182Accident · No injuriesClosedCessna 170B · San Miguel Is, CASep 7, 1968 · N1694D · DestroyedNTSB LAX69D0141Accident · No injuriesClosedCessna A188 · Sun City, AZSep 6, 1968 · N8112V · Substantial damageNTSB SEA69F0085Accident · No injuriesClosedCessna 210 · Boise, IDSep 4, 1968 · N3735Y · Substantial damageNTSB MIA69F0195Accident · No injuriesClosedPiper PA-18 · Sylvania, GASep 4, 1968 · N1035A · Substantial damageNTSB SEA69D0100Accident · No injuriesClosedPiper PA-25 · Othello, WASep 3, 1968 · N7610Z · Substantial damageNTSB FTW69F0163Accident · No injuriesClosedCessna 150 · Hattieville, ARSep 3, 1968 · N4040J · DestroyedNTSB MKC69D0243Accident · No injuriesClosedCessna 177 · Gravois Mills, MOSep 2, 1968 · N2955X · DestroyedNTSB MKC69D0123Accident · No injuriesClosedPiper PA-22 · Blue Rapids, KSSep 1, 1968 · N4690Z · Substantial damageNTSB LAX69F0124Accident · 1 fatalClosedBensen B-8M · Coolidge, AZSep 1, 1968 · N1573 · DestroyedNTSB FTW69D0230Accident · No injuriesClosedBoeing E75 · China, TXSep 1, 1968 · N7788B · Substantial damageNTSB CHI69F0253Accident · 1 fatalClosedAeronca 0-58A · Richland Ctr, WISep 1, 1968 · N47262 · DestroyedNTSB MIA69D0289Accident · No injuriesClosedPiper PA-28 · Hernando, FLAug 31, 1968 · N5749W · Substantial damageNTSB LAX69F0142Accident · 1 fatalClosedPiper PA-25 · Elfrida, AZAug 30, 1968 · N4834Y · DestroyedNTSB CHI69D0227Accident · Serious injuriesClosedBensen B-8M · Mt Vernon, INAug 30, 1968 · N6120 · Substantial damageNTSB ANC69F0069Accident · Serious injuriesClosedTaylorcraft BC12-D · Anchorage, AKAug 30, 1968 · N94907 · Substantial damageNTSB ANC69D0029Accident · Minor injuriesClosedAeronca S15AC · Anchorage, AKAug 30, 1968 · N1032H · Substantial damageNTSB MKC69F0210Accident · Serious injuriesClosedPiper PA-28 · Bonner Sprgs, KSAug 29, 1968 · N4917J · Substantial damageNTSB MKC69F0134Accident · No injuriesClosedCurtiss MR-1 · Eden Prairie, MNAug 29, 1968 · N91258 · Substantial damageNTSB LAX69F0137Accident · No injuriesClosedHughes 269A · Chula Vista, CAAug 28, 1968 · N8709F · Substantial damageNTSB FTW69F0235Accident · Minor injuriesClosedCessna 150G · Beaumont, TXAug 28, 1968 · N2654J · 2 aircraft · DestroyedNTSB FTW69D0198Accident · No injuriesClosedPiper PA-25 · Augusta, ARAug 28, 1968 · N7718Z · Substantial damageNTSB ANC69A0015Accident · No injuriesClosedCessna 180 · Fairbanks, AKAug 28, 1968 · N2769X · Substantial damageNTSB MKC69F0135Accident · No injuriesClosedAero Comdr 680T · Minneapolis, MNAug 27, 1968 · N7610U · Substantial damageNTSB MKC69F0111Accident · No injuriesClosedCessna 150G · Saronville, NEAug 27, 1968 · N3468J · Substantial damageNTSB MIA69A0010Accident · 2 fatalClosedPiper PA-24 · Bradenton, FLAug 26, 1968 · N7413P · DestroyedNTSB FTW69D0227Accident · No injuriesClosedBoeing E75 · Stowell, TXAug 26, 1968 · N5360N · Substantial damageNTSB NYC69D0154Accident · Serious injuriesClosedPiper PA-12 · Old Forge, NYAug 25, 1968 · N2825M · DestroyedNTSB LAX69D0085Accident · 4 fatalClosedCessna 180G · Punta Penasco, MexicoAug 25, 1968 · N2484R · DestroyedNTSB CHI69F0223Accident · No injuriesClosedPiper PA-32 · Effingham, ILAug 25, 1968 · N3344W · Substantial damageNTSB ANC69D0069Accident · No injuriesClosedCessna 172 · Soldotna, AKAug 25, 1968 · N5779R · Substantial damageNTSB MIA69D0124Accident · Minor injuriesClosedPiper PA-25 · Learned, MSAug 24, 1968 · N4548Y · Substantial damageNTSB ANC69A0013Accident · 2 fatalClosedTaylorcraft BC12-D · Anchorage, AKAug 24, 1968 · N96933 · DestroyedNTSB MKC69D0146Accident · No injuriesClosedPiper PA-25 · Scott City, KSAug 23, 1968 · N4804Y · Substantial damageNTSB FTW69D0176Accident · No injuriesClosedPiper PA-25 · Petersburg, TXAug 23, 1968 · N4859Y · Substantial damageNTSB ANC69D0054Accident · No injuriesClosedCessna 172H · Shungnak, AKAug 23, 1968 · N3710F · Substantial damageNTSB MIA69D0140Accident · No injuriesClosedMcneal RH HRH · Pompano Beach, FLAug 22, 1968 · N6312D · Substantial damageNTSB FTW69D0179Accident · No injuriesClosedPiper J-3 · San Antonio, TXAug 22, 1968 · N92100 · Substantial damageNTSB MIA69F0120Accident · No injuriesClosedPiper PA-25 · Eupora, MSAug 21, 1968 · N7008Z · DestroyedNTSB FTW69D0182Accident · Minor injuriesClosedMooney M20E · Henryetta, OKAug 21, 1968 · N79328 · Substantial damageNTSB DIA69D0052Accident · Minor injuriesClosedErcoupe 415-C · Lenoir, NCAug 21, 1968 · N3249H · DestroyedNTSB CHI69F0176Accident · No injuriesClosedPiper PA-28 · Ann Arbor, MIAug 21, 1968 · N9839W · Substantial damageNTSB MKC69F0066Accident · No injuriesClosedCessna 177 · Minneapolis, MNAug 19, 1968 · N2233Y · Substantial damageNTSB NYC69F0193Accident · Minor injuriesClosedCessna 150D · Ballston Spa, NYAug 18, 1968 · N5968T · Substantial damageNTSB CHI69A0022Accident · 4 fatalClosedAeronca 7EC · Jeffersonvlle, INAug 18, 1968 · N4649E · DestroyedNTSB OAK69D0112Accident · No injuriesClosedMooney M20E · Oakdale, CAAug 17, 1968 · N2566W · Substantial damageNTSB CHI69F0242Accident · No injuriesClosedNavion A · Harvard, ILAug 17, 1968 · N4005K · Substantial damageNTSB CHI69F0188Accident · No injuriesClosedBeech 23 · Mooresville, INAug 17, 1968 · N2324Q · Substantial damage