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 NYC66F0201Accident · Minor injuriesClosedBeechcraft 35 · Monroe, CTAug 25, 1965 · N3249V · Substantial damageNTSB MIA66F0250Accident · No injuriesClosedBoeing A-75N1 · Huntsville, ALAug 25, 1965 · N55134 · Substantial damageNTSB FTW66D0271Accident · No injuriesClosedStinson 108-1 · Houston, TXAug 25, 1965 · N312C · DestroyedNTSB DEN66F0113Accident · Minor injuriesClosedLuscombe 8A · Greely, COAug 25, 1965 · N45709 · Substantial damageNTSB CHI66A0021Accident · 1 fatalClosedNo American Navion · Findlay, OHAug 25, 1965 · N4519K · DestroyedNTSB LAX66D0102Accident · Minor injuriesClosedPiper PA-22 · Big Bear Lk, CAAug 24, 1965 · N4851A · Substantial damageNTSB MIA66F0202Accident · No injuriesClosedPiper PA-25 · Cochran, GAAug 23, 1965 · N7390Z · Substantial damageNTSB OAK66F0070Accident · Serious injuriesClosedLuscombe 8F · Angwin, CAAug 22, 1965 · N1262K · DestroyedNTSB NYC66D0167Accident · No injuriesClosedPiper PA-22 · Marlboro, MAAug 22, 1965 · N5674Z · Substantial damageNTSB MKC66D0198Accident · No injuriesClosedCessna 172 · Kaylor, SDAug 22, 1965 · N7499X · Substantial damageNTSB LAX66F0143Accident · Minor injuriesClosedConvair L-13A · Sparks, NVAug 22, 1965 · N68847 · Substantial damageNTSB MIA66F0244Accident · Serious injuriesClosedAeronca 7BCM · N.Smyrna Bech, FLAug 21, 1965 · N2517E · Substantial damageNTSB MIA66F0171Accident · No injuriesClosedPiper PA-25 · Harrison, GAAug 21, 1965 · N7497Z · DestroyedNTSB DEN66F0078Accident · Serious injuriesClosedNavion D-16 · Grand Canyon, AZAug 21, 1965 · N88W · DestroyedNTSB DEN66A0015Accident · 2 fatalClosedChampion 7EC · Cheyenne, WYAug 20, 1965 · N9569S · DestroyedNTSB CHI66F0191Accident · No injuriesClosedPiper PA-22 · Circleville, OHAug 20, 1965 · N3502P · Substantial damageNTSB MKC66F0211Accident · No injuriesClosedPiper PA-22 · Warrensburg, MOAug 19, 1965 · N8298C · Substantial damageNTSB FTW66F0477Accident · No injuriesClosedGrumman G-164 · Helena, ARAug 19, 1965 · N464RF · Substantial damageNTSB FTW66F0200Accident · No injuriesClosedPiper PA-25 · Sterlington, LAAug 18, 1965 · N7267Z · Substantial damageNTSB ANC66D0006Accident · No injuriesClosedCessna 180F · Bettles, AKAug 18, 1965 · N2684Y · DestroyedNTSB NYC66F0217Accident · Minor injuriesClosedBeechcraft C-45H · Cambridge, MDAug 17, 1965 · N159L · DestroyedNTSB MIA66F0194Accident · No injuriesClosedGrumman T-G164 · Charleston, MSAug 17, 1965 · N10278 · Substantial damageNTSB CHI66F0424Accident · Serious injuriesClosedPiper J-3 · De Pere, WIAug 17, 1965 · N23413 · Substantial damageNTSB CHI66F0209Accident · No injuriesClosedCessna 140 · Trivoli, ILAug 17, 1965 · N4158N · Substantial damageNTSB OAK66F0061Accident · Serious injuriesClosedHiller UH12E · Waikamoi, HIAug 16, 1965 · N5385V · DestroyedNTSB MIA66F0148Accident · No injuriesClosedGrumman G-164 · Smithville, GAAug 16, 1965 · N609U · DestroyedNTSB CHI66A0017Accident · No injuriesClosedNaa B-25J · Northbrook, ILAug 16, 1965 · N75755 · Substantial damageNTSB OAK66F0097Accident · No injuriesClosedFairchild WM-62 · Salinas, CAAug 15, 1965 · N48888 · Substantial damageNTSB MKC66F0238Accident · No injuriesClosedCessna 170 · Cottonwood, MNAug 15, 1965 · N4038V · Substantial damageNTSB MIA66F0183Accident · No injuriesClosedPiper PA-24 · Coloso, PRAug 15, 1965 · N7330P · Substantial damageNTSB LAX66F0082Accident · Serious injuriesClosedPiper J-2 · Huntington Bc, CAAug 15, 1965 · N15981 · DestroyedNTSB CHI66F0211Accident · Minor injuriesClosedLuscombe 8E · Plainfield, ILAug 15, 1965 · N71911 · Substantial damageNTSB ANC66F0022Accident · Serious injuriesClosedCessna U206 · Sitka, AKAug 15, 1965 · N2150F · DestroyedNTSB MIA66D0141Accident · Minor injuriesClosedErcoupe 415-G · Baxter, TNAug 14, 1965 · N94434 · Substantial damageNTSB LAX66F0114Accident · Serious injuriesClosedAeronca 7AC · Lone Pine, CAAug 14, 1965 · N2395E · DestroyedNTSB MKC66F0294Accident · No injuriesClosedTaylorcraft BC-12D · So St Paul, MNAug 13, 1965 · N96430 · Substantial damageNTSB MIA66A0019Accident · 1 fatalClosedBeechcraft A-35 · Statesville, NCAug 13, 1965 · N8595A · DestroyedNTSB FTW66A0017Accident · No injuriesClosedHughes 300AG · Briscoe, ARAug 13, 1965 · N9392F · Substantial damageNTSB NYC66F0203Accident · No injuriesClosedPiper PA-22 · Vincentown, NJAug 12, 1965 · N1417A · Substantial damageNTSB NYC66F0174Accident · Minor injuriesClosedRepublic RC-3 · Camillus, NYAug 12, 1965 · N6657K · Substantial damageNTSB LAX66A0019Accident · No injuriesClosedHughes 269A · Fraizer Prk, CAAug 12, 1965 · N8796F · Substantial damageNTSB CHI66F0229Accident · No injuriesClosedPiper PA-22 · Hinckley, ILAug 12, 1965 · N6023D · Substantial damageNTSB NYC66D0218Accident · No injuriesClosedCessna 150 · New City, NYAug 11, 1965 · N5951T · Substantial damageNTSB FTW66F0198Accident · No injuriesClosedGrumman G-164 · Welsh, LAAug 11, 1965 · N10275 · Substantial damageNTSB NYC66F0158Accident · Serious injuriesClosedPiper PA-15 · Lowell, MAAug 10, 1965 · N4392H · DestroyedNTSB NYC66F0133Accident · Serious injuriesClosedAeronca 7AC · Warrington, PAAug 10, 1965 · N2477E · Substantial damageNTSB LAX66F0064Accident · No injuriesClosedCessna 120 · Santa Maria, CAAug 10, 1965 · N77446 · Substantial damageNTSB ANC66D0016Accident · No injuriesClosedPiper PA-14 · Ketchikan, AKAug 10, 1965 · N5161H · Substantial damageNTSB LAX66D0129Accident · No injuriesClosedCessna 120 · Santa Maria, CAAug 9, 1965 · N77446 · Substantial damageNTSB LAX66A0018Accident · 1 fatalClosedCougar SNJN11 · Corona, CAAug 9, 1965 · N3136G · Destroyed