SQL on FHIR
2.1.0-pre - release
SQL on FHIR - Local Development build (v2.1.0-pre) built by the FHIR (HL7® FHIR® Standard) Build Tools. See the Directory of published versions
Official URL: https://sql-on-fhir.org/ig/Library/UniquePatientAddressesQuery | Version: 2.1.0-pre | |||
Active as of 2025-07-31 | Computable Name: UniquePatientAddressesQuery |
This is an example of a query library that has a few dialects:
application/sql
-- Standard SQL
WITH RankedAddresses AS (
SELECT
pd.*,
pa.*,
ROW_NUMBER() OVER (PARTITION BY pd.patient_id ORDER BY pa.address_id) AS address_rank
FROM
patient_demographics pd
JOIN
patient_addresses pa ON pd.patient_id = pa.patient_id
WHERE
pd.age > 18
AND pa.city = New York
)
application/sql; dialect=sql-2
SELECT pd.*, pa.*
FROM patient_demographics pd
JOIN patient_addresses pa ON pd.patient_id = pa.patient_id
WHERE pd.age > 18
AND pa.city = New York
AND pa.address_id = (
SELECT MIN(address_id)
FROM patient_addresses
WHERE patient_id = pd.patient_id AND city = New York
);
Profile: SQL Query Library
Depends On | https://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition/PatientDemographics |
Depends On | https://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition/PatientAddresses |
application/sql
-- Standard SQL
WITH RankedAddresses AS (
SELECT
pd.*,
pa.*,
ROW_NUMBER() OVER (PARTITION BY pd.patient_id ORDER BY pa.address_id) AS address_rank
FROM
patient_demographics pd
JOIN
patient_addresses pa ON pd.patient_id = pa.patient_id
WHERE
pd.age > 18
AND pa.city = New York
)
application/sql;dialect=sql-2
SELECT pd.*, pa.*
FROM patient_demographics pd
JOIN patient_addresses pa ON pd.patient_id = pa.patient_id
WHERE pd.age > 18
AND pa.city = New York
AND pa.address_id = (
SELECT MIN(address_id)
FROM patient_addresses
WHERE patient_id = pd.patient_id AND city = New York
);