Airworthiness directives that may apply · 319

duckdb -csv -c "SELECT d.ad_number, d.status, d.subject, d.effective_date, m.matched_by
  FROM (
    SELECT ad_id, min(rank) AS rank, arg_min(matched_by, rank) AS matched_by
    FROM (SELECT ad_id, 1 AS rank, 'airframe' AS matched_by FROM 'https://openairdocs.org/data/latest/faa_aircraft_model_directive.parquet' WHERE aircraft_model_code = '3023501'
      UNION ALL SELECT ad_id, 2, 'engine' FROM 'https://openairdocs.org/data/latest/faa_engine_model_directive.parquet' WHERE engine_model_code = '30018')
    GROUP BY ad_id
  ) m JOIN 'https://openairdocs.org/data/latest/faa_directive.parquet' d USING (ad_id)
  ORDER BY d.status = 'Historical', m.rank, coalesce(d.effective_date, d.publication_date, d.issue_date) DESC, d.ad_number 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 d.ad_number, d.status, d.subject, d.effective_date, m.matched_by
FROM (
  SELECT ad_id, min(rank) AS rank, arg_min(matched_by, rank) AS matched_by
  FROM (SELECT ad_id, 1 AS rank, 'airframe' AS matched_by FROM 'https://openairdocs.org/data/latest/faa_aircraft_model_directive.parquet' WHERE aircraft_model_code = '3023501'
    UNION ALL SELECT ad_id, 2, 'engine' FROM 'https://openairdocs.org/data/latest/faa_engine_model_directive.parquet' WHERE engine_model_code = '30018')
  GROUP BY ad_id
) m JOIN 'https://openairdocs.org/data/latest/faa_directive.parquet' d USING (ad_id)
ORDER BY d.status = 'Historical', m.rank, coalesce(d.effective_date, d.publication_date, d.issue_date) DESC, d.ad_number 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.