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 MIA69F0099Accident · Minor injuriesClosedPiper PA-25 · Eufala, ALJul 27, 1968 · N4908Y · DestroyedNTSB DIA69F0040Accident · Serious injuriesClosedAeronca 7AC · Benson, NCJul 27, 1968 · N84893 · Substantial damageNTSB MKC69F0058Accident · No injuriesClosedPiper PA-18 · Sutton, NEJul 26, 1968 · N7476D · Substantial damageNTSB SEA69F0049Accident · Minor injuriesClosedCessna 188 · Sunnyside, WAJul 25, 1968 · N8024V · Substantial damageNTSB DEN69D0056Accident · No injuriesClosedHughes 269B · Buffalo, WYJul 25, 1968 · N9460F · Substantial damageNTSB ANC69A0003Accident · No injuriesClosedBell 47G-2 · Minchumina, AKJul 25, 1968 · N6708D · Substantial damageNTSB MIA69F0155Accident · No injuriesClosedHughes 269A · Cope, SCJul 24, 1968 · N8893F · Substantial damageNTSB FTW69F0194Accident · No injuriesClosedBoeing B75N1 · Muleshoe, TXJul 24, 1968 · N4522N · Substantial damageNTSB FTW69F0070Accident · Serious injuriesClosedPiper PA-25 · Madisonville, TXJul 24, 1968 · N7584Z · DestroyedNTSB OAK69F0049Accident · Serious injuriesClosedBriegleb BG-12B · Reno, NVJul 23, 1968 · N12940 · DestroyedNTSB MIA69D0136Accident · No injuriesClosedCallair A-9 · Estill, SCJul 22, 1968 · N7244V · DestroyedNTSB SEA69A0007Accident · 1 fatalClosedCommonwlth 9000 · Vancouver, WAJul 21, 1968 · N18008 · DestroyedNTSB MIA69D0123Accident · Minor injuriesClosedAeronca 7ECA · Cedartown, GAJul 21, 1968 · N11043 · DestroyedNTSB CHI69F0177Accident · No injuriesClosedPiper J3C-65 · Cynthiana, KYJul 21, 1968 · N32754 · Substantial damageNTSB CHI69F0142Accident · Minor injuriesClosedPiper PA-22 · Put In Bay, OHJul 21, 1968 · N2928Z · Substantial damageNTSB CAN68F0499Accident · 1 fatalClosedRepublic RC-3 · Harris Lake, CanadaJul 21, 1968 · N1426V · DestroyedNTSB OAK69I0006Incident · Minor injuriesClosedBoeing 727 · Seattle, WAJul 20, 1968 · N7562U · No damageNTSB OAK69F0105Accident · No injuriesClosedBeech A35 · Tah0e Valley, CAJul 20, 1968 · N536B · Substantial damageNTSB NYC69F0105Accident · No injuriesClosedPiper PA-22 · Leechburg, PAJul 20, 1968 · N3656Z · Substantial damageNTSB NYC69D0060Accident · No injuriesClosedPiper J-3 · Hamlin, PAJul 20, 1968 · N21574 · Substantial damageNTSB CHI69A0008Accident · 1 fatalClosedPiper J3L · Sullivan, ILJul 20, 1968 · N32743 · Substantial damageNTSB UNK68X0157Accident · Minor injuriesClosedBeech F35 · Alberta, CanadaJul 19, 1968 · N3806B · Substantial damageNTSB OAK69D0066Accident · No injuriesClosedCessna 337B · Fresno, CAJul 19, 1968 · N8314G · Substantial damageNTSB MKC69F0095Accident · Minor injuriesClosedPiper J3C · Austin, MNJul 19, 1968 · N6535H · DestroyedNTSB FTW69D0099Accident · No injuriesClosedTaylorcraft BC-65 · Broken Arrow, OKJul 19, 1968 · N22636 · Substantial damageNTSB ANC69D0012Accident · No injuriesClosedPiper PA-12 · Soldotna, AKJul 19, 1968 · N2812M · Substantial damageNTSB MKC69A0002Accident · No injuriesClosedPiper PA-32 · Denison, IAJul 18, 1968 · N3235W · Substantial damageNTSB MIA69F0071Accident · Minor injuriesClosedCessna 170 · Deland, FLJul 18, 1968 · N1452D · DestroyedNTSB LAX69D0061Accident · No injuriesClosedBoeing A75N1 · Blythe, CAJul 18, 1968 · N48788 · Substantial damageNTSB FTW69F0094Accident · Minor injuriesClosedPiper PA-25 · Amarillo, TXJul 18, 1968 · N6180Z · DestroyedNTSB FTW69D0146Accident · Minor injuriesClosedGrumman G-164 · Brinkley, ARJul 18, 1968 · N10294 · DestroyedNTSB FTW69D0072Accident · No injuriesClosedPiper PA-18 · Earth, TXJul 18, 1968 · N3970Z · Substantial damageNTSB CHI69D0111Accident · No injuriesClosedPiper J3C-65 · Crystal Lake, ILJul 18, 1968 · N5740N · Substantial damageNTSB SEA69D0037Accident · No injuriesClosedPiper PA-25 · Othello, WAJul 17, 1968 · N7561Z · Substantial damageNTSB NYC69F0088Accident · Serious injuriesClosedScheibe SF27M · Hopewell Tnshp, NJJul 17, 1968 · N6301 · Substantial damageNTSB NYC69F0048Accident · No injuriesClosedCessna 150F · Medford, NJJul 17, 1968 · N6486F · Substantial damageNTSB DIA69F0151Accident · Serious injuriesClosedCessna 182L · Nitro, WVJul 17, 1968 · N42188 · DestroyedNTSB FTW69F0118Accident · No injuriesClosedPiper PA-25 · Arkadelphia, ARJul 15, 1968 · N6318Z · DestroyedNTSB ANC69D0040Accident · No injuriesClosedBell 47G-2 · Minchumina, AKJul 14, 1968 · N6711D · Substantial damageNTSB UNK68X0154Accident · Serious injuriesClosedPiper PA-32 · Vermilion Bay, CanadaJul 13, 1968 · N5530J · Substantial damageNTSB OAK69D0048Accident · No injuriesClosedCessna 172 · Reno, NVJul 13, 1968 · N4493L · Substantial damageNTSB NYC69F0084Accident · Serious injuriesClosedCessna 177 · Red Bank, NJJul 13, 1968 · N2259Y · DestroyedNTSB NYC69F0061Accident · Minor injuriesClosedCessna 175 · Marlboro, MAJul 12, 1968 · N9472B · DestroyedNTSB FTW69F0106Accident · No injuriesClosedPiper PA-25 · Mccrory, ARJul 12, 1968 · N7089Z · Substantial damageNTSB CHI69F0107Accident · Serious injuriesClosedLuscombe 8E · Watervliet, MIJul 12, 1968 · N1654K · DestroyedNTSB NYC69F0035Accident · No injuriesClosedAeronca 7AC · Matamoras, PAJul 11, 1968 · N3035E · Substantial damageNTSB FTW69F0105Accident · Minor injuriesClosedPiper PA-25 · N.Little Rock, ARJul 11, 1968 · N6032Z · Substantial damageNTSB SEA69A0003Accident · 4 fatalClosedCessna 172H · Stehekin, WAJul 9, 1968 · N2854L · DestroyedNTSB LAX69F0046Accident · 1 fatalClosedPiper PA-25 · Maryvale, AZJul 9, 1968 · N4482Y · DestroyedNTSB FTW69F0045Accident · No injuriesClosedPiper PA-25 · Newellton, LAJul 9, 1968 · N4732Y · Substantial damage