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 LAX66F0052Accident · No injuriesClosedBeechcraft 95-B55 · San Diego, CAJul 22, 1965 · N296B · Substantial damageNTSB FTW66F0184Accident · No injuriesClosedCessna 180 · Keene, TXJul 22, 1965 · N1705C · Substantial damageNTSB MKC66D0102Accident · No injuriesClosedBeechcraft H-35 · Cresco, IAJul 21, 1965 · N5565D · Substantial damageNTSB CHI66F0123Accident · No injuriesClosedPiper PA-24 · Benton Harbor, MIJul 21, 1965 · N6051P · Substantial damageNTSB ANC65F0012Accident · Serious injuriesClosedPiper JC3 · Ketchikan, AKJul 21, 1965 · N35533 · DestroyedNTSB NYC66F0155Accident · No injuriesClosedPiper PA-22 · Niagara Falls, NYJul 20, 1965 · N9711D · Substantial damageNTSB LAX66F0024Accident · No injuriesClosedCallair A-9 · Orovada, NVJul 20, 1965 · N8248H · DestroyedNTSB CHI66F0109Accident · Minor injuriesClosedPiper PA-32 · Elgin, ILJul 20, 1965 · N3207W · Substantial damageNTSB NYC66F0188Accident · No injuriesClosedStinson 108-3 · Amherst, NYJul 18, 1965 · N860C · Substantial damageNTSB FTW66F0181Accident · No injuriesClosedBeechcraft C-18S · Houston, TXJul 18, 1965 · N445N · DestroyedNTSB NYC66F0095Accident · Serious injuriesClosedWaco UPF7 · Manchester, VTJul 17, 1965 · N32168 · DestroyedNTSB NYC66F0064Accident · Serious injuriesClosedPiper J3C · Baltimore, MDJul 17, 1965 · N70429 · Substantial damageNTSB MKC66F0094Accident · Minor injuriesClosedPiper PA-18 · Fremont, NEJul 17, 1965 · N7489D · Substantial damageNTSB MIA66A0010Accident · 8 fatalClosedDehavilland 106A · St.Thomas, VIJul 15, 1965 · N6503D · DestroyedNTSB MIA66A0009Accident · 1 fatalClosedPiper PA-18A · Jacksonville, GAJul 14, 1965 · N9922D · DestroyedNTSB OAK66F0007Accident · Minor injuriesClosedBoeing A75N1 · Yuba City, CAJul 13, 1965 · N52023 · Substantial damageNTSB NYC66A0007Accident · 5 fatalClosedBeechcraft C-45G · Parkersburg, WVJul 13, 1965 · N2818G · DestroyedNTSB MKC66F0158Accident · No injuriesClosedStinson 108-3 · Grand Portage, MNJul 13, 1965 · N6630M · Substantial damageNTSB FTW66F0156Accident · No injuriesClosedLuscombe 8A · Westwego, LAJul 13, 1965 · N71267 · Substantial damageNTSB FTW66D0177Accident · No injuriesClosedGruman G-164 · Hunter, ARJul 13, 1965 · N714Y · Substantial damageNTSB NYC66F0108Accident · Serious injuriesClosedAero Cmdr 560 · Danbury, CTJul 12, 1965 · N2654B · Substantial damageNTSB MIA66F0063Accident · Minor injuriesClosedTaylorcraft BC12D · Mineral Sps, NCJul 12, 1965 · N96185 · Substantial damageNTSB ANC66F0032Accident · No injuriesClosedCessna 180B · Anchorage, AKJul 12, 1965 · N9128T · Substantial damageNTSB MKC66F0164Accident · No injuriesClosedPiper PA-25 · Nosseo, MNJul 11, 1965 · N6150Z · Substantial damageNTSB MIA66F0086Accident · No injuriesClosedPiper PA-25 · Batesville, MSJul 11, 1965 · N7507Z · Substantial damageNTSB MIA66A0006Accident · No injuriesClosedBell 47-D1 · Panama City, FLJul 11, 1965 · N3109G · Substantial damageNTSB CHI66F0095Accident · 1 fatalClosedSmith Mini DSA-1 · Macomb, ILJul 11, 1965 · N21Z · DestroyedNTSB CHI66A0006Accident · 2 fatalClosedCessna 310 · Detroit, MIJul 11, 1965 · N6811T · DestroyedNTSB MKC66F0112Accident · No injuriesClosedMooney M20C · Cainsville, MOJul 10, 1965 · N64561 · DestroyedNTSB MKC66D0135Accident · Minor injuriesClosedPiper PA-16 · Jadwin, MOJul 10, 1965 · N5365H · DestroyedNTSB MKC66D0128Accident · Minor injuriesClosedPiper PA-22 · Hallsville, MOJul 10, 1965 · N2811P · Substantial damageNTSB MIA66F0084Accident · Minor injuriesClosedBeech C-18S · Marathon, FLJul 10, 1965 · N7871B · DestroyedNTSB CHI66F0080Accident · No injuriesClosedBeechcraft A-23 · Troy, MIJul 9, 1965 · N2338Q · Substantial damageNTSB CHI66F0166Accident · No injuriesClosedStinson 108 · Lac Duflambeu, WIJul 8, 1965 · N6865M · Substantial damageNTSB UNK65X1516Accident · No injuriesClosedPiper PA-22 · Casa Grande, AZJul 7, 1965 · N1583P · DestroyedNTSB MKC66F0090Accident · Minor injuriesClosedPiper PA-18 · Terre Haute, INJul 7, 1965 · N5457H · Substantial damageNTSB LAX66F0019Accident · No injuriesClosedAeronca 7AC · Grass Valley, CAJul 7, 1965 · N84537 · Substantial damageNTSB MKC66F0111Accident · Minor injuriesClosedPiper J3 · Eagleville, MOJul 6, 1965 · N70555 · DestroyedNTSB NYC66D0098Accident · No injuriesClosedPiper J-3 · Redington Pond, MEJul 5, 1965 · N49618 · Substantial damageNTSB MKC66D0085Accident · No injuriesClosedBeechcraft 23 · Branson, MOJul 5, 1965 · N2390J · Substantial damageNTSB MIA66F0080Accident · Minor injuriesClosedCessna 150 · Tiptonville, TNJul 5, 1965 · N7094X · Substantial damageNTSB CHI66F0044Accident · Serious injuriesClosedCessna 170B · Herndon, KYJul 5, 1965 · N2276D · DestroyedNTSB CHI66A0004Accident · 1 fatalClosedAeronca 7AC · Pulaski, KYJul 5, 1965 · N2009E · DestroyedNTSB LAX66D0051Accident · No injuriesClosedWaco UPF-7 · Adelanto, CAJul 4, 1965 · N29318 · DestroyedNTSB CHI66F0364Accident · No injuriesClosedPiper PA-12 · Dallas, WIJul 4, 1965 · N4227M · Substantial damageNTSB NYC66F0070Accident · No injuriesClosedPiper PA-22 · Cumberland, MDJul 3, 1965 · N8816D · Substantial damageNTSB MKC66F0070Accident · Minor injuriesClosedChampion 7GCB · Imperial, NEJul 3, 1965 · N9937Y · Substantial damageNTSB CHI66A0001Accident · 2 fatalClosedTaylorcraft BC-12D · Mineral Point, WIJul 3, 1965 · N96648 · DestroyedNTSB CHI66D0105Accident · No injuriesClosedPiper J-3 · Naperville, CAJul 2, 1965 · N88556 · Substantial damageNTSB MIA66A0001Accident · 1 fatalClosedPiper PA-32 · Vero Beach, FLJul 1, 1965 · N9998W · Destroyed