SQL on FHIR
2.1.0-pre - release International flag

SQL on FHIR, published by SQL on FHIR Working Group. This guide is not an authorized publication; it is the continuous build for version 2.1.0-pre built by the FHIR (HL7® FHIR® Standard) CI Build. This version is based on the current content of https://github.com/FHIR/sql-on-fhir-v2/ and changes regularly. See the Directory of published versions

Library: UniquePatientAddressesQuery

Official URL: https://sql-on-fhir.org/ig/Library/UniquePatientAddressesQuery Version: 2.1.0-pre
Active as of 2025-09-04 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
  );

Related Artifacts

Depends Onhttps://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition/PatientDemographics
Depends Onhttps://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition/PatientAddresses

Contents

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
  );