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 NYC68F0462Accident · Minor injuriesClosedPiper PA-15 · Rapid City, SDMar 3, 1968 · N4481H · Substantial damageNTSB DEN68D0213Accident · No injuriesClosedBellanca 17-30 · Agua Fria, NMMar 3, 1968 · N6693V · Substantial damageNTSB FTW68F0474Accident · Minor injuriesClosedCessna 170A · Beedeville, ARMar 2, 1968 · N9511A · DestroyedNTSB MIA68A0073Accident · No injuriesClosedBeech D18S · Immokalee, FLMar 1, 1968 · N78N · Substantial damageNTSB NYC68F0437Accident · No injuriesClosedCessna 182J · Butler, PAFeb 29, 1968 · N3524F · Substantial damageNTSB DIA68A0033Accident · No injuriesClosedDouglas DC-3C · Statesville, NCFeb 29, 1968 · N525W · Substantial damageNTSB MKC68D0496Accident · No injuriesClosedPiper PA-28 · Oakes, NDFeb 27, 1968 · N8687W · Substantial damageNTSB OAK68A0061Accident · 2 fatalClosedAeronca 7BCM · Hopland, CAFeb 26, 1968 · N82743 · DestroyedNTSB FTW68F0494Accident · No injuriesClosedCessna 177 · Grandview, TXFeb 25, 1968 · N3180T · DestroyedNTSB CHI68F0633Accident · No injuriesClosedCessna 170 · Chicago, ILFeb 25, 1968 · N9024A · Substantial damageNTSB OAK68D0317Accident · No injuriesClosedCessna 140 · Oceano, CAFeb 24, 1968 · N76139 · Substantial damageNTSB LAX68D0342Accident · No injuriesClosedCessna 177 · Oxnard, CAFeb 21, 1968 · N3291T · Substantial damageNTSB FTW68F0464Accident · No injuriesClosedCessna 182J · Andrews, TXFeb 21, 1968 · N3112F · Substantial damageNTSB ANC68D0098Accident · No injuriesClosedCessna 170 · Mcgrath, AKFeb 19, 1968 · N3478D · Substantial damageNTSB DIA68F0166Accident · No injuriesClosedCessna 210D · Robbinsville, NCFeb 18, 1968 · N3949Y · Substantial damageNTSB DEN68A0037Accident · 3 fatalClosedPiper PA-24 · Fry Canyon, UTFeb 18, 1968 · N8158P · DestroyedNTSB ANC68F0109Accident · No injuriesClosedCessna 180 · Skagway, AKFeb 18, 1968 · N3314D · DestroyedNTSB MIA69F0473Accident · No injuriesClosedBeech TC 45G · Cayce, SCFeb 17, 1968 · N199H · DestroyedNTSB FTW68D0452Accident · No injuriesClosedBeech N35 · Plainview, TXFeb 15, 1968 · N992Q · Substantial damageNTSB MKC68A0052Accident · Serious injuriesClosedCessna P206A · Fort Madison, IAFeb 12, 1968 · N4653F · Substantial damageNTSB MKC68F0454Accident · Minor injuriesClosedBeech K35 · Oakley, KSFeb 11, 1968 · N5334E · Substantial damageNTSB DEN68F0215Accident · Minor injuriesClosedStinson 108-3 · Boulder, COFeb 11, 1968 · N524C · Substantial damageNTSB ANC68D0101Accident · No injuriesClosedPiper PA-22 · Hughes, AKFeb 11, 1968 · N4549A · Substantial damageNTSB MKC68D0436Accident · No injuriesClosedCessna 140 · Thief Rvr Fl, MNFeb 10, 1968 · CF-PJP · Substantial damageNTSB LAX68D0338Accident · No injuriesClosedBellanca 14-13 · Colton, CAFeb 10, 1968 · N74328 · Substantial damageNTSB NYC68A0098Accident · 3 fatalClosedPiper PA-23 · Wappngrs Falls, NYFeb 9, 1968 · N5877Y · DestroyedNTSB MIA68D0418Accident · No injuriesClosedBeech A23 · Key West, FLFeb 9, 1968 · N2377W · Substantial damageNTSB CHI68A0070Accident · No injuriesClosedBell 47J · Harrison, OHFeb 9, 1968 · N2895B · Substantial damageNTSB FTW68F0434Accident · No injuriesClosedCessna 150 · Alexandria, LAFeb 8, 1968 · N2915J · Substantial damageNTSB CHI68F0614Accident · Serious injuriesClosedCessna 140 · Terre Haute, INFeb 8, 1968 · N76407 · Substantial damageNTSB CHI68F0585Accident · Minor injuriesClosedPiper PA-23 · Gahanna, OHFeb 7, 1968 · N1198P · Substantial damageNTSB MIA68D0468Accident · No injuriesClosedBeech 95-D55 · Orangeburg, SCFeb 5, 1968 · N7092N · Substantial damageNTSB CHI68F0593Accident · No injuriesClosedCessna 337B · Auburn, INFeb 5, 1968 · N5441S · Substantial damageNTSB MKC68F0447Accident · No injuriesClosedPiper J3C-65 · Fergus Falls, MNFeb 4, 1968 · N7025B · Substantial damageNTSB LAX68D0322Accident · Minor injuriesClosedPratt-Read PR-G1 · La Jolla, CAFeb 4, 1968 · N63171 · Substantial damageNTSB FTW68F0432Accident · No injuriesClosedCessna 337 · Schulenberg, TXFeb 4, 1968 · N6310F · Substantial damageNTSB CHI68D0591Accident · No injuriesClosedPiper PA-28 · Franklin, INFeb 4, 1968 · N3883K · Substantial damageNTSB CHI68A0068Accident · 1 fatalClosedPiper PA-22 · Cleveland, OHFeb 4, 1968 · N8404D · DestroyedNTSB CHI68F0573Accident · Minor injuriesClosedCessna 150G · Midlothian, ILFeb 3, 1968 · N3200J · Substantial damageNTSB CHI68F0601Accident · No injuriesClosedPiper PA-23 · Northbrook, ILFeb 2, 1968 · N4342P · Substantial damageNTSB MIA68D0405Accident · Minor injuriesClosedAeronca 7AC · Jacksboro, TNJan 31, 1968 · N1648E · Substantial damageNTSB LAX68D0314Accident · No injuriesClosedCessna 206 · Pinon, AZJan 30, 1968 · N3809G · Substantial damageNTSB CHI68F0595Accident · No injuriesClosedTemco D-16 · Sycamore, ILJan 30, 1968 · N70N · Substantial damageNTSB CHI68F0589Accident · No injuriesClosedAero Comdr 500 · Columbus, INJan 30, 1968 · N22000 · Substantial damageNTSB FTW68F0437Accident · No injuriesClosedMooney M20C · Amarillo, TXJan 28, 1968 · N7700M · Substantial damageNTSB DEN68F0217Accident · Minor injuriesClosedPiper PA-28 · Ittleton, COJan 28, 1968 · N9690J · DestroyedNTSB SEA68F0256Accident · Serious injuriesClosedAeronca 65-CA · Ashland, ORJan 27, 1968 · N39353 · DestroyedNTSB MIA68F0396Accident · No injuriesClosedPiper PA-28 · Metter, GAJan 27, 1968 · N9602W · Substantial damageNTSB DIA68F0154Accident · No injuriesClosedPiper PA-18 · Brookneal, VAJan 25, 1968 · N2979Z · Substantial damageNTSB NYC68D0376Accident · Minor injuriesClosedCessna 150 · Kingston, NYJan 21, 1968 · N22196 · Substantial damage