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 FTW69D0003Accident · No injuriesClosedPiper PA-25 · Edinburg, TXMay 20, 1968 · N6110Z · DestroyedNTSB LAX68F0423Accident · No injuriesClosedPiper PA-16 · Long Beach, CAMay 19, 1968 · N5348H · Substantial damageNTSB DEN69F0005Accident · No injuriesClosedCessna 172 · Ogden, UTMay 19, 1968 · N6920A · Substantial damageNTSB NYC68D0503Accident · No injuriesClosedPiper PA-12 · Paul Smiths, NYMay 18, 1968 · N2615M · Substantial damageNTSB MIA69F0032Accident · Serious injuriesClosedPiper J3L-65 · Dalton, GAMay 18, 1968 · N32839 · DestroyedNTSB FTW68A0093Accident · 4 fatalClosedMooney M20F · Dallas, TXMay 18, 1968 · N3502X · DestroyedNTSB ANC69D0013Accident · Serious injuriesClosedAeronca 7EC · Anchorage, AKMay 18, 1968 · N7448B · DestroyedNTSB MKC68D0586Accident · No injuriesClosedPiper PA-28 · Mount Vernon, IAMay 17, 1968 · N5172L · Substantial damageNTSB FTW68D0590Accident · No injuriesClosedBoeing A75 · Huffman, TXMay 17, 1968 · N54035 · DestroyedNTSB CHI69D0018Accident · Minor injuriesClosedPiper PA-28 · Cadiz, OHMay 17, 1968 · N3427K · DestroyedNTSB SEA68F0314Accident · No injuriesClosedPiper PA-25 · Townsend, MTMay 16, 1968 · N7306Z · Substantial damageNTSB LAX69F0020Accident · Serious injuriesClosedNavion G · Upland, ARMay 16, 1968 · N2356T · DestroyedNTSB DIA68D0227Accident · No injuriesClosedCessna 150E · Hertford, NCMay 16, 1968 · N3991U · Substantial damageNTSB OAK68F0387Accident · Minor injuriesClosedCessna 140 · Soquel, CAMay 15, 1968 · N2155N · Substantial damageNTSB MIA68D0545Accident · No injuriesClosedCessna 172 · Naples, FLMay 15, 1968 · N7718X · DestroyedNTSB CHI68A0096Accident · 1 fatalClosedBeech D18 · Marshfield, WIMay 15, 1968 · N1977D · DestroyedNTSB OAK69F0200Accident · No injuriesClosedCessna 402 · Kaunakakai, HIMay 12, 1968 · N4033Q · Substantial damageNTSB MKC68F0581Accident · Minor injuriesClosedCessna 177 · Venturia, NDMay 12, 1968 · N3358T · DestroyedNTSB LAX68A0100Accident · 3 fatalClosedCessna 172 · Apple Valley, CAMay 12, 1968 · N8077B · DestroyedNTSB DEN68D0261Accident · No injuriesClosedCessna 150H · Cheraw, COMay 12, 1968 · N6987S · Substantial damageNTSB CHI69F0010Accident · Serious injuriesClosedN.American AT-6 · Naperville, ILMay 12, 1968 · N2805G · Substantial damageNTSB CHI69D0037Accident · No injuriesClosedBeech C35 · Delavan, WIMay 12, 1968 · N1920D · Substantial damageNTSB SEA68D0316Accident · No injuriesClosedCessna 188 · Lancaster, WAMay 11, 1968 · N5507S · Substantial damageNTSB NYC68F0474Accident · No injuriesClosedPiper PA-24 · Medford, NJMay 11, 1968 · N8865P · Substantial damageNTSB LAX68F0413Accident · No injuriesClosedLuscombe 8A · Hntngtn Bch, CAMay 11, 1968 · N45767 · Substantial damageNTSB NYC68F0488Accident · 1 fatalClosedMustang RS-1 · Endicott, NYMay 7, 1968 · N1100R · DestroyedNTSB DEN68A0052Accident · No injuriesClosedPiper PA-23 · Conejos, COMay 7, 1968 · N6139Y · Substantial damageNTSB FTW68F0609Accident · No injuriesClosedBell-Carson 47G · San Antonio, TXMay 5, 1968 · N614X · Substantial damageNTSB FTW68F0584Accident · No injuriesClosedStinson 108-2 · Alexandria, LAMay 5, 1968 · N361C · Substantial damageNTSB FTW68D0585Accident · No injuriesClosedPiper PA-25 · Terral, OKMay 5, 1968 · N6918Z · Substantial damageNTSB MKC68F0570Accident · No injuriesClosedBensen B-8 · Winsted, MNMay 4, 1968 · N1075Z · Substantial damageNTSB DIA68F0219Accident · Minor injuriesClosedPiper PA-22 · Oak Grove, VAMay 4, 1968 · N8880D · DestroyedNTSB OAK68D0370Accident · 1 fatalClosedCessna 140 · Volcano, CAMay 3, 1968 · N2058V · Substantial damageNTSB FTW68D0579Accident · No injuriesClosedGrumman G-164 · Kaplan, LAMay 2, 1968 · N10281 · Substantial damageNTSB FTW68F0607Accident · Serious injuriesClosedSchweizer SGS233 · Friendswood, TXMay 1, 1968 · N2480W · Minor damageNTSB FTW68F0577Accident · Minor injuriesClosedBeech A23 · Sasakwa, OKMay 1, 1968 · N4770J · Substantial damageNTSB MKC68F0567Accident · No injuriesClosedPiper PA-22 · Buffalo, MNApr 29, 1968 · N4509A · Substantial damageNTSB CHI68D0733Accident · No injuriesClosedCessna 172 · Ottawa, ILApr 29, 1968 · N4289L · Substantial damageNTSB FTW68F0566Accident · No injuriesClosedCessna 177 · Marfa, TXApr 28, 1968 · N2348Y · Substantial damageNTSB CHI68F0760Accident · Minor injuriesClosedCallair A-2 · New Haven, INApr 28, 1968 · N33351 · Substantial damageNTSB NYC69D0019Accident · No injuriesClosedGlobe GC-1B · Titusville, PAApr 27, 1968 · N78063 · Substantial damageNTSB MKC68F0580Accident · Serious injuriesClosedStinson 108-2 · Menomonie, WIApr 27, 1968 · N973D · DestroyedNTSB MIA68D0511Accident · Minor injuriesClosedCessna 150 · Moore Haven, FLApr 27, 1968 · N8816G · Substantial damageNTSB LAX68A0095Accident · 4 fatalClosedWaco UIC · S.Luis Obispo, CAApr 27, 1968 · N13066 · DestroyedNTSB CHI68F0722Accident · No injuriesClosedPiper PA-23 · Findlay, OHApr 25, 1968 · N5752Y · Substantial damageNTSB LAX68D0403Accident · Minor injuriesClosedBeech A23 · Whetstone, AZApr 24, 1968 · N8784M · Substantial damageNTSB FTW68D0558Accident · No injuriesClosedGrumman G164 · Oberlin, LAApr 23, 1968 · N591Y · Substantial damageNTSB NYC68F0454Accident · No injuriesClosedCessna 150 · Winchendon, MAApr 22, 1968 · N8058S · Substantial damageNTSB CHI68F0740Accident · Serious injuriesClosedN.American P-51D · Springfield, ILApr 21, 1968 · N2114 · DestroyedNTSB FTW68F0534Accident · No injuriesClosedPiper PA-28 · Canton, TXApr 20, 1968 · N4238J · Substantial damage