Skip to content

NTSB investigations: Piper PA-23

331 cases on aircraft the registry lists as Piper PA-23. Aircraft are matched to the registry by N-number and event date, checked against the reported serial number, make and model, so older matches can be wrong.

Defining event

Finding

Phase of flight

Aircraft model

Cases, newest first

Query with SQL

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 x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('7102302')) ORDER BY c."eventDate" DESC, c."ntsbNumber" DESC

duckdb -csv -c "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 x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('7102302'))
  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.

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 x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('7102302'))
ORDER BY c."eventDate" DESC, c."ntsbNumber" DESC

Run it at the DuckDB prompt, or in any tool that reads Parquet over HTTPS. More ways to query the release are on Use the data.

NTSB MIA77FLD36Accident · No injuriesClosedPiper PA-23 · Winter Park, FLMar 18, 1977 · N3112P · Substantial damage NTSB MIA77FLA17Accident · Serious injuriesClosedPiper PA-23 · Lake City, FLFeb 23, 1977 · N1370P · Substantial damage NTSB CHI77FA017Accident · 1 fatalClosedPiper PA-23 · North Royalton, OHDec 23, 1976 · N2264P · Destroyed NTSB IAD77FLJ01Accident · Minor injuriesClosedPiper PA-23 · Angier, NCDec 18, 1976 · N1054P · Substantial damage NTSB FTW77FA004Accident · 4 fatalClosedPiper PA-23 · College Sta, TXOct 16, 1976 · N1434P · Destroyed NTSB FTW76AF112Accident · 1 fatalClosedPiper PA-23 · Dalhart, TXAug 2, 1976 · N1459P · Destroyed NTSB SEA76DYI22Accident · No injuriesClosedPiper PA-23 · Lakeview, ORJul 12, 1976 · N3154P · Substantial damage NTSB NYC76FNA42Accident · Serious injuriesClosedPiper PA-23 · Greene, RIJul 9, 1976 · N2277P · Destroyed NTSB MIA76FKG58Accident · Serious injuriesClosedPiper PA-23 · Jonesboro, GAApr 24, 1976 · N2068P · Destroyed NTSB OAK76FVG64Accident · Minor injuriesClosedPiper PA-23 · Alturas, CAApr 4, 1976 · N1092P · Destroyed NTSB DEN76AD042Accident · 4 fatalClosedPiper PA-23 · Lusk, WYMar 20, 1976 · N3080P · Destroyed NTSB NYC76FNA30Accident · No injuriesClosedPiper PA-23 · Warwick, RIMar 8, 1976 · N2277P · Substantial damage NTSB NYC76FNA27Accident · No injuriesClosedPiper PA-23 · Bedford, MAJan 17, 1976 · N3006P · Substantial damage NTSB CHI76FEE54Accident · No injuriesClosedPiper PA-23 · Crestwood, ILJan 4, 1976 · N11TJ · Substantial damage NTSB NYC76AN052Accident · 1 fatalClosedPiper PA-23 · Spring Valley, NYDec 4, 1975 · N2191P · Destroyed NTSB OAK76AP027Accident · 2 fatalClosedPiper PA-23 · Orinda, CANov 5, 1975 · N1225P · Destroyed NTSB MIA76FKG18Accident · No injuriesClosedPiper PA-23 · Jonesboro, GAAug 22, 1975 · N2068P · Substantial damage NTSB DEN76FTE05Accident · No injuriesClosedPiper PA-23 · Lake City, COJul 7, 1975 · N5RJ · Substantial damage NTSB LAX76FUA02Accident · No injuriesClosedPiper PA-23 · Torrance, CAJul 1, 1975 · N1052P · Substantial damage NTSB FTW75FPD49Accident · No injuriesClosedPiper PA-23 · Grand Prairie, TXJun 27, 1975 · N1495P · Substantial damage NTSB FTW75AF052Accident · 2 fatalClosedPiper PA-23 · New Orleans, LAFeb 17, 1975 · N4321P · Destroyed NTSB NYC75AN079Accident · 1 fatalClosedPiper PA-23 · Stratford, CTJan 10, 1975 · N4322Y · Substantial damage NTSB CHI75DEE37Accident · No injuriesClosedPiper PA-23 · Monee, ILDec 23, 1974 · N2207P · Substantial damage NTSB MKC75AK031Accident · 2 fatalClosedPiper PA-23 · Perryville, MODec 18, 1974 · N2223R · Destroyed NTSB FTW75FPG29Accident · Serious injuriesClosedPiper PA-23 · Brickeys, ARNov 27, 1974 · N1375P · Substantial damage NTSB NYC75FHD26Accident · Minor injuriesClosedPiper PA-23 · Ocean City, NJNov 24, 1974 · N1MV · Substantial damage NTSB FTW75AF035Accident · 2 fatalClosedPiper PA-23 · Falfurrias, TXNov 17, 1974 · N1395P · Destroyed NTSB CHI75FET10Accident · No injuriesClosedPiper PA-23 · Hobart, INOct 31, 1974 · N142X · Substantial damage NTSB FTW75FRJ17Accident · Minor injuriesClosedPiper PA-23 · Leesville, LAOct 19, 1974 · N1043P · Substantial damage NTSB IAD75AI015Accident · 2 fatalClosedPiper PA-23 · Pinnacle, NCSep 26, 1974 · N2193P · Destroyed NTSB MKC75FTG12Accident · No injuriesClosedPiper PA-23 · Bowman, NDSep 4, 1974 · N1136P · Substantial damage NTSB NYC75FGM06Accident · No injuriesClosedPiper PA-23 · Washington, PAAug 2, 1974 · N2000P · Substantial damage NTSB NYC75FHA07Accident · No injuriesClosedPiper PA-23 · Middleville, NJAug 1, 1974 · N2008P · Substantial damage NTSB NYC75FHM02Accident · Serious injuriesClosedPiper PA-23 · Montgomery, NYJul 13, 1974 · N1067P · Substantial damage NTSB FTW74FRH01Accident · No injuriesClosedPiper PA-23 · Corpus Chrsti, TXJun 22, 1974 · N321W · Substantial damage NTSB ANC74AA078Accident · 1 fatalClosedPiper PA-23 · Fairbanks, AKJun 2, 1974 · N117ME · Destroyed NTSB MIA74DKT70Accident · No injuriesClosedPiper PA-23 · Opa Locka, FLApr 16, 1974 · N2075P · Substantial damage NTSB OAK74DXQ31Accident · No injuriesClosedPiper PA-23 · Ely, NVApr 12, 1974 · N1038P · Substantial damage NTSB OAK74DVA58Accident · No injuriesClosedPiper PA-23 · Stratford, CAApr 10, 1974 · N1074P · Substantial damage NTSB IAD74DHD74Accident · No injuriesClosedPiper PA-23 · Dover, DEFeb 8, 1974 · N13RD · Substantial damage NTSB CHI74FEC26Accident · No injuriesClosedPiper PA-23 · Marlette, MIJan 1, 1974 · N2201P · Substantial damage NTSB NYC74FHA22Accident · Minor injuriesClosedPiper PA-23 · Reading, PADec 23, 1973 · N1478P · Destroyed NTSB IAD74AI028Accident · 2 fatalClosedPiper PA-23 · Heaton, NCNov 25, 1973 · N1391P · Destroyed NTSB FTW74AF038Accident · 2 fatalClosedPiper PA-23 · Navasota, TXNov 25, 1973 · N2067P · Destroyed NTSB CHI74FEA17Accident · Minor injuriesClosedPiper PA-23 · Terre Haute, INOct 26, 1973 · N1365P · Destroyed NTSB FTW74AF022Accident · 1 fatalClosedPiper PA-23 · Liberty, TXOct 12, 1973 · N3188P · Destroyed NTSB IAD74FLT05Accident · No injuriesClosedPiper PA-23 · Memphis, TNAug 20, 1973 · N1130P · Substantial damage NTSB CHI74AC009Accident · 1 fatalClosedPiper PA-23 · Chicago, ILJul 27, 1973 · N1483P · Destroyed NTSB MIA74DLD07Accident · No injuriesClosedPiper PA-23 · Ocala, FLJul 22, 1973 · N3075P · Substantial damage NTSB IAD74AI005Accident · 3 fatalClosedPiper PA-23 · Deep Run, NCJul 17, 1973 · N3117P · Destroyed