{
  "description": "Retrieves each patient's most recent address in a given city. Includes two\\ndialects for the same logical query.\\n\\n**Standard SQL:**\\n```sql\\nWITH ranked_addresses AS (\\n  SELECT\\n    patient_view.id AS patient_id,\\n    patient_view.name,\\n    patient_address_view.address_line1,\\n    patient_address_view.city,\\n    patient_address_view.state,\\n    patient_address_view.postal_code,\\n    patient_address_view.updated_at,\\n    ROW_NUMBER() OVER (\\n      PARTITION BY patient_view.id\\n      ORDER BY patient_address_view.updated_at DESC, patient_address_view.address_id DESC\\n    ) AS address_rank\\n  FROM patient_view\\n  JOIN patient_address_view\\n    ON patient_view.id = patient_address_view.patient_id\\n  WHERE patient_view.active = true\\n    AND patient_address_view.city = :city\\n)\\nSELECT\\n  patient_id,\\n  name,\\n  address_line1,\\n  city,\\n  state,\\n  postal_code\\nFROM ranked_addresses\\nWHERE address_rank = 1\\n```\\n\\n**PostgreSQL dialect:**\\n```sql\\nSELECT DISTINCT ON (patient_view.id)\\n  patient_view.id AS patient_id,\\n  patient_view.name,\\n  patient_address_view.address_line1,\\n  patient_address_view.city,\\n  patient_address_view.state,\\n  patient_address_view.postal_code\\nFROM patient_view\\nJOIN patient_address_view\\n  ON patient_view.id = patient_address_view.patient_id\\nWHERE patient_view.active = true\\n  AND patient_address_view.city = :city\\nORDER BY patient_view.id, patient_address_view.updated_at DESC, patient_address_view.address_id DESC\\n```",
  "_filename": "example/Library-UniquePatientAddressesQuery.json",
  "package_name": "hl7.fhir.uv.sql-on-fhir",
  "date": "2026-08-14T02:49:31+10:00",
  "meta": {
    "profile": [ "http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/SQLQuery" ]
  },
  "publisher": "HL7 International / FHIR Infrastructure",
  "jurisdiction": [ {
    "coding": [ {
      "code": "001",
      "system": "http://unstats.un.org/unsd/methods/m49/m49.htm",
      "display": "World"
    } ]
  } ],
  "content": [ {
    "data": "V0lUSCByYW5rZWRfYWRkcmVzc2VzIEFTICgKICBTRUxFQ1QKICAgIHBhdGllbnRfdmlldy5pZCBBUyBwYXRpZW50X2lkLAogICAgcGF0aWVudF92aWV3Lm5hbWUsCiAgICBwYXRpZW50X2FkZHJlc3Nfdmlldy5hZGRyZXNzX2xpbmUxLAogICAgcGF0aWVudF9hZGRyZXNzX3ZpZXcuY2l0eSwKICAgIHBhdGllbnRfYWRkcmVzc192aWV3LnN0YXRlLAogICAgcGF0aWVudF9hZGRyZXNzX3ZpZXcucG9zdGFsX2NvZGUsCiAgICBwYXRpZW50X2FkZHJlc3Nfdmlldy51cGRhdGVkX2F0LAogICAgUk9XX05VTUJFUigpIE9WRVIgKAogICAgICBQQVJUSVRJT04gQlkgcGF0aWVudF92aWV3LmlkCiAgICAgIE9SREVSIEJZIHBhdGllbnRfYWRkcmVzc192aWV3LnVwZGF0ZWRfYXQgREVTQywgcGF0aWVudF9hZGRyZXNzX3ZpZXcuYWRkcmVzc19pZCBERVNDCiAgICApIEFTIGFkZHJlc3NfcmFuawogIEZST00gcGF0aWVudF92aWV3CiAgSk9JTiBwYXRpZW50X2FkZHJlc3NfdmlldwogICAgT04gcGF0aWVudF92aWV3LmlkID0gcGF0aWVudF9hZGRyZXNzX3ZpZXcucGF0aWVudF9pZAogIFdIRVJFIHBhdGllbnRfdmlldy5hY3RpdmUgPSB0cnVlCiAgICBBTkQgcGF0aWVudF9hZGRyZXNzX3ZpZXcuY2l0eSA9IDpjaXR5CikKU0VMRUNUCiAgcGF0aWVudF9pZCwKICBuYW1lLAogIGFkZHJlc3NfbGluZTEsCiAgY2l0eSwKICBzdGF0ZSwKICBwb3N0YWxfY29kZQpGUk9NIHJhbmtlZF9hZGRyZXNzZXMKV0hFUkUgYWRkcmVzc19yYW5rID0gMQ==",
    "extension": [ {
      "url": "http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/sql-text",
      "valueString": "WITH ranked_addresses AS (\n  SELECT\n    patient_view.id AS patient_id,\n    patient_view.name,\n    patient_address_view.address_line1,\n    patient_address_view.city,\n    patient_address_view.state,\n    patient_address_view.postal_code,\n    patient_address_view.updated_at,\n    ROW_NUMBER() OVER (\n      PARTITION BY patient_view.id\n      ORDER BY patient_address_view.updated_at DESC, patient_address_view.address_id DESC\n    ) AS address_rank\n  FROM patient_view\n  JOIN patient_address_view\n    ON patient_view.id = patient_address_view.patient_id\n  WHERE patient_view.active = true\n    AND patient_address_view.city = :city\n)\nSELECT\n  patient_id,\n  name,\n  address_line1,\n  city,\n  state,\n  postal_code\nFROM ranked_addresses\nWHERE address_rank = 1"
    } ],
    "contentType": "application/sql"
  }, {
    "data": "U0VMRUNUIERJU1RJTkNUIE9OIChwYXRpZW50X3ZpZXcuaWQpCiAgcGF0aWVudF92aWV3LmlkIEFTIHBhdGllbnRfaWQsCiAgcGF0aWVudF92aWV3Lm5hbWUsCiAgcGF0aWVudF9hZGRyZXNzX3ZpZXcuYWRkcmVzc19saW5lMSwKICBwYXRpZW50X2FkZHJlc3Nfdmlldy5jaXR5LAogIHBhdGllbnRfYWRkcmVzc192aWV3LnN0YXRlLAogIHBhdGllbnRfYWRkcmVzc192aWV3LnBvc3RhbF9jb2RlCkZST00gcGF0aWVudF92aWV3CkpPSU4gcGF0aWVudF9hZGRyZXNzX3ZpZXcKICBPTiBwYXRpZW50X3ZpZXcuaWQgPSBwYXRpZW50X2FkZHJlc3Nfdmlldy5wYXRpZW50X2lkCldIRVJFIHBhdGllbnRfdmlldy5hY3RpdmUgPSB0cnVlCiAgQU5EIHBhdGllbnRfYWRkcmVzc192aWV3LmNpdHkgPSA6Y2l0eQpPUkRFUiBCWSBwYXRpZW50X3ZpZXcuaWQsIHBhdGllbnRfYWRkcmVzc192aWV3LnVwZGF0ZWRfYXQgREVTQywgcGF0aWVudF9hZGRyZXNzX3ZpZXcuYWRkcmVzc19pZCBERVND",
    "extension": [ {
      "url": "http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/sql-text",
      "valueString": "SELECT DISTINCT ON (patient_view.id)\n  patient_view.id AS patient_id,\n  patient_view.name,\n  patient_address_view.address_line1,\n  patient_address_view.city,\n  patient_address_view.state,\n  patient_address_view.postal_code\nFROM patient_view\nJOIN patient_address_view\n  ON patient_view.id = patient_address_view.patient_id\nWHERE patient_view.active = true\n  AND patient_address_view.city = :city\nORDER BY patient_view.id, patient_address_view.updated_at DESC, patient_address_view.address_id DESC"
    } ],
    "contentType": "application/sql;dialect=postgresql"
  } ],
  "name": "UniquePatientAddressesQuery",
  "type": "{:coding [{:system \\\"http://hl7.org/fhir/uv/sql-on-fhir/CodeSystem/LibraryTypesCodes\\\", :code \\\"sql-query\\\"}]}",
  "experimental": null,
  "resourceType": "Library",
  "title": "Unique Patient Addresses",
  "package_version": "3.0.0-ballot",
  "status": "active",
  "id": "128d95c2-6711-4192-8874-e09a875f50ff",
  "kind": null,
  "url": "http://hl7.org/fhir/uv/sql-on-fhir/Library/UniquePatientAddressesQuery",
  "identifier": [ {
    "value": "urn:oid:2.16.840.1.113883.4.642.40.77.28.5",
    "system": "urn:ietf:rfc:3986"
  } ],
  "version": "3.0.0-ballot",
  "relatedArtifact": [ {
    "type": "depends-on",
    "label": "patient_view",
    "resource": "https://example.org/ViewDefinition/patient_view"
  }, {
    "type": "depends-on",
    "label": "patient_address_view",
    "resource": "https://example.org/ViewDefinition/patient_address_view"
  } ],
  "contact": [ {
    "name": "HL7 International / FHIR Infrastructure",
    "telecom": [ {
      "value": "http://www.hl7.org/Special/committees/fiwg",
      "system": "url"
    } ]
  } ],
  "parameter": [ {
    "use": "in",
    "name": "city",
    "type": "string",
    "documentation": "City to filter addresses"
  } ]
}