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: Unique Patient Addresses

Official URL: https://sql-on-fhir.org/ig/Library/UniquePatientAddressesQuery Version: 2.1.0-pre
Active as of 2026-02-24 Computable Name: UniquePatientAddressesQuery

Retrieves each patient’s most recent address in a given city. Includes two dialects for the same logical query.

Standard SQL:

WITH ranked_addresses AS (
  SELECT
    patient_view.id AS patient_id,
    patient_view.name,
    patient_address_view.address_line1,
    patient_address_view.city,
    patient_address_view.state,
    patient_address_view.postal_code,
    patient_address_view.updated_at,
    ROW_NUMBER() OVER (
      PARTITION BY patient_view.id
      ORDER BY patient_address_view.updated_at DESC, patient_address_view.address_id DESC
    ) AS address_rank
  FROM patient_view
  JOIN patient_address_view
    ON patient_view.id = patient_address_view.patient_id
  WHERE patient_view.active = true
    AND patient_address_view.city = :city
)
SELECT
  patient_id,
  name,
  address_line1,
  city,
  state,
  postal_code
FROM ranked_addresses
WHERE address_rank = 1

PostgreSQL dialect:

SELECT DISTINCT ON (patient_view.id)
  patient_view.id AS patient_id,
  patient_view.name,
  patient_address_view.address_line1,
  patient_address_view.city,
  patient_address_view.state,
  patient_address_view.postal_code
FROM patient_view
JOIN patient_address_view
  ON patient_view.id = patient_address_view.patient_id
WHERE patient_view.active = true
  AND patient_address_view.city = :city
ORDER BY patient_view.id, patient_address_view.updated_at DESC, patient_address_view.address_id DESC

Related Artifacts

Depends Onpatient_viewhttps://example.org/ViewDefinition/patient_view
Depends Onpatient_address_viewhttps://example.org/ViewDefinition/patient_address_view

Parameters

cityinstringCity to filter addresses

Contents

application/sql

WITH ranked_addresses AS (
  SELECT
    patient_view.id AS patient_id,
    patient_view.name,
    patient_address_view.address_line1,
    patient_address_view.city,
    patient_address_view.state,
    patient_address_view.postal_code,
    patient_address_view.updated_at,
    ROW_NUMBER() OVER (
      PARTITION BY patient_view.id
      ORDER BY patient_address_view.updated_at DESC, patient_address_view.address_id DESC
    ) AS address_rank
  FROM patient_view
  JOIN patient_address_view
    ON patient_view.id = patient_address_view.patient_id
  WHERE patient_view.active = true
    AND patient_address_view.city = :city
)
SELECT
  patient_id,
  name,
  address_line1,
  city,
  state,
  postal_code
FROM ranked_addresses
WHERE address_rank = 1

application/sql;dialect=postgresql

SELECT DISTINCT ON (patient_view.id)
  patient_view.id AS patient_id,
  patient_view.name,
  patient_address_view.address_line1,
  patient_address_view.city,
  patient_address_view.state,
  patient_address_view.postal_code
FROM patient_view
JOIN patient_address_view
  ON patient_view.id = patient_address_view.patient_id
WHERE patient_view.active = true
  AND patient_address_view.city = :city
ORDER BY patient_view.id, patient_address_view.updated_at DESC, patient_address_view.address_id DESC