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 FTW67I0033Incident · No injuriesClosedBeech D18 · Houston, TXSep 9, 1966 · N441MH · Substantial damageNTSB CHI68D0039Accident · Minor injuriesClosedGlobe GC-1B · Polo, ILSep 9, 1966 · N78082 · Substantial damageNTSB FTW67A0032Accident · No injuriesClosedHughes 269B · Hale Center, TXSep 8, 1966 · N9341F · Substantial damageNTSB CHI67D0206Accident · No injuriesClosedMooney M20E · Bloomington, ILSep 8, 1966 · N3485X · Substantial damageNTSB SEA67D0151Accident · No injuriesClosedCessna 175 · Bellevue, WASep 7, 1966 · N7272M · Substantial damageNTSB OAK67A0030Accident · Minor injuriesClosedGrumman TBM · Elko, NVSep 6, 1966 · N6894C · DestroyedNTSB DEN67F0122Accident · No injuriesClosedPiper PA-24 · New Castle, WYSep 6, 1966 · N5579P · Substantial damageNTSB CHI67F0296Accident · Serious injuriesClosedPiper PA-22 · Toronto, OHSep 6, 1966 · N7445D · DestroyedNTSB CHI67F0191Accident · Minor injuriesClosedAeronca 15AC · Superior, WISep 6, 1966 · N1156H · Substantial damageNTSB OAK67A0029Accident · 4 fatalClosedBellanca 14-132 · Tahoe Paradis, CASep 5, 1966 · N74381 · DestroyedNTSB NYC67A0045Accident · 1 fatalClosedPiper PA-28 · Flushing, NYSep 5, 1966 · N8754W · Substantial damageNTSB FTW67F0196Accident · Minor injuriesClosedStinson 108 · St.Francisvlle, LASep 4, 1966 · N97163 · Substantial damageNTSB SEA67A0020Accident · No injuriesClosedHiller UH-12E · Cottage Grve, ORSep 3, 1966 · N9732C · Substantial damageNTSB MIA67A0032Accident · 2 fatalClosedSchweizer TG-3A · Lewisburg, TNSep 3, 1966 · N1436M · DestroyedNTSB UNK66X0169AccidentClosedBeech G35 · Seattle, WASep 1, 1966 · N45590 · Substantial damageNTSB NYC67A0042Accident · 2 fatalClosedHughes 269B · Tuxedo, MDSep 1, 1966 · N9359F · DestroyedNTSB DEN67D0080Accident · Minor injuriesClosedStinson 108-3 · Whites City, NMSep 1, 1966 · N969C · Substantial damageNTSB FTW67D0209Accident · No injuriesClosedBeech 23 · Lake Texoma, OKAug 31, 1966 · N2310L · Substantial damageNTSB MKC67A0019Accident · No injuriesClosedBrantly B-2A · Center Point, IAAug 30, 1966 · N2282U · Substantial damageNTSB DEN67D0142Accident · No injuriesClosedCessna P206A · Santa Rosa, NMAug 29, 1966 · N2693X · Substantial damageNTSB NYC67F0197Accident · No injuriesClosedCessna 182J · Coxsackie, NYAug 28, 1966 · N3471F · DestroyedNTSB NYC67D0226Accident · No injuriesClosedAeronca 11AC · Marion, VAAug 28, 1966 · N9450E · Substantial damageNTSB MKC67F0185Accident · Minor injuriesClosedCessna 170B · Clarissa, MNAug 28, 1966 · N2714D · Substantial damageNTSB CHI67F0226Accident · No injuriesClosedBeech A35 · Midlothian, ILAug 28, 1966 · N564B · Substantial damageNTSB CHI67A0029Accident · 1 fatalClosedSchemp SHK-1 · Saginaw, MIAug 28, 1966 · N30055 · DestroyedNTSB ANC67D0020Accident · No injuriesClosedPiper PA-14 · Ketchikan, AKAug 28, 1966 · N4275H · Substantial damageNTSB OAK67F0094Accident · Minor injuriesClosedPiper PA-28 · Petaluma, CAAug 27, 1966 · N8606W · Substantial damageNTSB NYC67D0215Accident · No injuriesClosedCessna 150 · Clinton, MDAug 27, 1966 · N8615F · Substantial damageNTSB FTW67F0187Accident · Minor injuriesClosedBellanca 14-13 · Trinity, TXAug 27, 1966 · N74407 · DestroyedNTSB FTW67F0186Accident · No injuriesClosedPiper PA-25 · Pawnee, TXAug 26, 1966 · N6436Z · Substantial damageNTSB CHI67F0195Accident · Serious injuriesClosedPiper PA-25 · Sylvania, GAAug 26, 1966 · N7525Z · DestroyedNTSB CHI67D0196Accident · No injuriesClosedCessna 172 · Vestaburg, MIAug 26, 1966 · N8487U · Substantial damageNTSB SEA67F0214Accident · No injuriesClosedPiper PA-22 · Hillsboro, ORAug 24, 1966 · N2931P · Substantial damageNTSB SEA67F0133Accident · No injuriesClosedStinson 108-3 · Hamilton, MTAug 22, 1966 · N6669M · Substantial damageNTSB ANC67F0057Accident · No injuriesClosedMooney M20E · Chisana, AKAug 22, 1966 · N6971U · Substantial damageNTSB SEA67F0163Accident · No injuriesClosedGlobe GC-1B · Vancouver, WAAug 21, 1966 · N80649 · Substantial damageNTSB SEA67F0158Accident · No injuriesClosedCessna 310 · Hillsboro, ORAug 21, 1966 · N3028D · Substantial damageNTSB SEA67D0103Accident · No injuriesClosedMooney M-18 · Easton, WAAug 21, 1966 · N4180 · Substantial damageNTSB MKC67D0239Accident · No injuriesClosedCessna 172 · Mexico City, MexicoAug 20, 1966 · N4383L · Substantial damageNTSB MIA67D0163Accident · No injuriesClosedTaylorcraft BC12-D · Brandon, FLAug 20, 1966 · N95840 · Substantial damageNTSB DEN67F0125Accident · Serious injuriesClosedPiper PA-25 · Deming, NMAug 20, 1966 · N6121Z · DestroyedNTSB CHI67F0265Accident · No injuriesClosedGlobe GC-1B · Gladwin, MIAug 20, 1966 · N3232K · Substantial damageNTSB SEA67F0157Accident · No injuriesClosedPiper PA-24 · Oregon City, ORAug 19, 1966 · N7798P · Substantial damageNTSB MIA67A0026Accident · 1 fatalClosedPiper PA-18A · Eufaula Ala, ALAug 19, 1966 · N8675C · DestroyedNTSB ANC67A0020Accident · No injuriesClosedHiller UH-12E · Dillingham, AKAug 19, 1966 · N5341V · Substantial damageNTSB MIA67F0237Accident · No injuriesClosedPiper PA-25 · Rosier, GAAug 18, 1966 · N6606Z · Substantial damageNTSB CHI67D0233Accident · No injuriesClosedMooney M20C · Chicago, ILAug 18, 1966 · N2758W · Substantial damageNTSB CHI67D0147Accident · No injuriesClosedPiper PA-22 · Fremont, OHAug 17, 1966 · N7466R · Substantial damageNTSB SEA67F0099Accident · No injuriesClosedBoeing A75N1 · Sunnyside, WAAug 16, 1966 · N1153N · Substantial damageNTSB MKC67D0151Accident · No injuriesClosedLuscombe 8A · Lone Jack, MOAug 16, 1966 · N45792 · Substantial damage