Fatal NTSB investigations: Piper PA-24
78 cases on aircraft the registry lists as Piper PA-24. 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.
Cases, newest first
Query with SQL
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.\"highestInjuryLevel\" = 'Fatal'
AND c.\"mKey\" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('7102402'))
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."highestInjuryLevel" = 'Fatal'
AND c."mKey" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('7102402'))
ORDER BY c."eventDate" DESC, c."ntsbNumber" DESCRun 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 MIA71AM056Accident · 1 fatalClosedPiper PA-24 · Barney, GANTSB SEA71AS028Accident · 1 fatalClosedPiper PA-24 · Kimberly, IDNTSB CHI71AC034Accident · 4 fatalClosedPiper PA-24 · Weston, WINTSB ANC71AA023Accident · 1 fatalClosedPiper PA-24 · Fairbanks, AKNTSB SEA70A0016Accident · 3 fatalClosedPiper PA-24 · Newberg, ORNTSB FTW69A0084Accident · 1 fatalClosedPiper PA-24 · Boydville, ARNTSB CHI69A0084Accident · 4 fatalClosedPiper PA-24 · Aledo, ILNTSB NYC69A0087Accident · 4 fatalClosedPiper PA-24 · Ogletown, PANTSB FTW69A0060Accident · 4 fatalClosedCessna 150 · Manvel, TXNTSB FTW69A0036Accident · 3 fatalClosedPiper PA-24 · Lampasas, TXNTSB NYC69A0047Accident · 2 fatalClosedPiper PA-24 · Fleming, PANTSB MIA69A0010Accident · 2 fatalClosedPiper PA-24 · Bradenton, FLNTSB MKC68A0059Accident · 1 fatalClosedCessna 150 · Kansas City, MONTSB DIA68A0042Accident · 2 fatalClosedPiper PA-24 · Robbinsville, NCNTSB NYC67A0079Accident · 2 fatalClosedPiper PA-24 · Spencer, WVNTSB CHI67A0026Accident · 2 fatalClosedPiper PA-24 · New Lexingtn, OHNTSB MKC67A0009Accident · 4 fatalClosedPiper PA-24 · Hunter, KSNTSB MIA66A0106Accident · 2 fatalClosedPiper PA-24 · Burnsville, NCNTSB SEA66A0045Accident · 2 fatalClosedPiper PA-24 · New Plymouth, IDNTSB MIA66A0076Accident · 2 fatalClosedPiper PA-24 · Steele, ALNTSB DEN66A0009Accident · 3 fatalClosedPiper PA-24 · Ruidoso, NMNTSB NYC65A0156Accident · 1 fatalClosedCessna 170 · Neffsville, PANTSB NYC65A0128Accident · 2 fatalClosedPiper PA-24 · Johnstown, PANTSB CHI65A0088Accident · 2 fatalClosedPiper PA-24 · Flint, MINTSB UNK65X0842Accident · 1 fatalClosedPiper PA-24 · El Dorado, ARNTSB UNK64X3095Accident · 1 fatalClosedPiper PA-24 · Elizabethtown, KYNTSB UNK64X0764Accident · 1 fatalClosedPiper PA22 · Birmingham, MINTSB LAX94LA336Accident · 4 fatalLimited investigationClosedPiper PA24-180 · Bridgeport, CAIn-flight collision with terrain for unknown reason(s).