SQL on FHIR
2.1.0-pre - release
SQL on FHIR
2.1.0-pre - release
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
| Active as of 2026-02-24 |
{
"resourceType" : "Library",
"id" : "UniquePatientAddressesQuery",
"meta" : {
"profile" : [
🔗 "https://sql-on-fhir.org/ig/StructureDefinition/SQLQuery"
]
},
"text" : {
"status" : "generated",
"div" : "<div xmlns=\"http://www.w3.org/1999/xhtml\"><p class=\"res-header-id\"><b>Generated Narrative: Library UniquePatientAddressesQuery</b></p><a name=\"UniquePatientAddressesQuery\"> </a><a name=\"hcUniquePatientAddressesQuery\"> </a><div style=\"display: inline-block; background-color: #d9e0e7; padding: 6px; margin: 4px; border: 1px solid #8da1b4; border-radius: 5px; line-height: 60%\"><p style=\"margin-bottom: 0px\"/><p style=\"margin-bottom: 0px\">Profile: <a href=\"StructureDefinition-SQLQuery.html\">SQL Query Library</a></p></div><h2>Related Artifacts</h2><table class=\"grid\"><tr><td>Depends On</td><td>patient_view</td><td><code>https://example.org/ViewDefinition/patient_view</code></td></tr><tr><td>Depends On</td><td>patient_address_view</td><td><code>https://example.org/ViewDefinition/patient_address_view</code></td></tr></table><h2>Parameters</h2><table class=\"grid\"><tr><td>city</td><td>in</td><td/><td/><td>string</td><td>City to filter addresses</td></tr></table><h2>Contents</h2><p><code>application/sql</code></p><pre><code>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</code></pre><p><code>application/sql;dialect=postgresql</code></p><pre><code>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</code></pre></div>"
},
"url" : "https://sql-on-fhir.org/ig/Library/UniquePatientAddressesQuery",
"version" : "2.1.0-pre",
"name" : "UniquePatientAddressesQuery",
"title" : "Unique Patient Addresses",
"status" : "active",
"type" : {
"coding" : [
{
"system" : "https://sql-on-fhir.org/ig/CodeSystem/LibraryTypesCodes",
"code" : "sql-query"
}
]
},
"date" : "2026-02-24T21:00:17+00:00",
"publisher" : "SQL on FHIR Working Group",
"contact" : [
{
"name" : "SQL on FHIR Working Group",
"telecom" : [
{
"system" : "url",
"value" : "https://sql-on-fhir.org"
}
]
}
],
"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```",
"jurisdiction" : [
{
"coding" : [
{
"system" : "http://unstats.un.org/unsd/methods/m49/m49.htm",
"code" : "001",
"display" : "World"
}
]
}
],
"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"
}
],
"parameter" : [
{
"name" : "city",
"use" : "in",
"documentation" : "City to filter addresses",
"type" : "string"
}
],
"content" : [
{
"extension" : [
{
"url" : "https://sql-on-fhir.org/ig/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" : "V0lUSCByYW5rZWRfYWRkcmVzc2VzIEFTICgKICBTRUxFQ1QKICAgIHBhdGllbnRfdmlldy5pZCBBUyBwYXRpZW50X2lkLAogICAgcGF0aWVudF92aWV3Lm5hbWUsCiAgICBwYXRpZW50X2FkZHJlc3Nfdmlldy5hZGRyZXNzX2xpbmUxLAogICAgcGF0aWVudF9hZGRyZXNzX3ZpZXcuY2l0eSwKICAgIHBhdGllbnRfYWRkcmVzc192aWV3LnN0YXRlLAogICAgcGF0aWVudF9hZGRyZXNzX3ZpZXcucG9zdGFsX2NvZGUsCiAgICBwYXRpZW50X2FkZHJlc3Nfdmlldy51cGRhdGVkX2F0LAogICAgUk9XX05VTUJFUigpIE9WRVIgKAogICAgICBQQVJUSVRJT04gQlkgcGF0aWVudF92aWV3LmlkCiAgICAgIE9SREVSIEJZIHBhdGllbnRfYWRkcmVzc192aWV3LnVwZGF0ZWRfYXQgREVTQywgcGF0aWVudF9hZGRyZXNzX3ZpZXcuYWRkcmVzc19pZCBERVNDCiAgICApIEFTIGFkZHJlc3NfcmFuawogIEZST00gcGF0aWVudF92aWV3CiAgSk9JTiBwYXRpZW50X2FkZHJlc3NfdmlldwogICAgT04gcGF0aWVudF92aWV3LmlkID0gcGF0aWVudF9hZGRyZXNzX3ZpZXcucGF0aWVudF9pZAogIFdIRVJFIHBhdGllbnRfdmlldy5hY3RpdmUgPSB0cnVlCiAgICBBTkQgcGF0aWVudF9hZGRyZXNzX3ZpZXcuY2l0eSA9IDpjaXR5CikKU0VMRUNUCiAgcGF0aWVudF9pZCwKICBuYW1lLAogIGFkZHJlc3NfbGluZTEsCiAgY2l0eSwKICBzdGF0ZSwKICBwb3N0YWxfY29kZQpGUk9NIHJhbmtlZF9hZGRyZXNzZXMKV0hFUkUgYWRkcmVzc19yYW5rID0gMQ=="
},
{
"extension" : [
{
"url" : "https://sql-on-fhir.org/ig/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",
"data" : "U0VMRUNUIERJU1RJTkNUIE9OIChwYXRpZW50X3ZpZXcuaWQpCiAgcGF0aWVudF92aWV3LmlkIEFTIHBhdGllbnRfaWQsCiAgcGF0aWVudF92aWV3Lm5hbWUsCiAgcGF0aWVudF9hZGRyZXNzX3ZpZXcuYWRkcmVzc19saW5lMSwKICBwYXRpZW50X2FkZHJlc3Nfdmlldy5jaXR5LAogIHBhdGllbnRfYWRkcmVzc192aWV3LnN0YXRlLAogIHBhdGllbnRfYWRkcmVzc192aWV3LnBvc3RhbF9jb2RlCkZST00gcGF0aWVudF92aWV3CkpPSU4gcGF0aWVudF9hZGRyZXNzX3ZpZXcKICBPTiBwYXRpZW50X3ZpZXcuaWQgPSBwYXRpZW50X2FkZHJlc3Nfdmlldy5wYXRpZW50X2lkCldIRVJFIHBhdGllbnRfdmlldy5hY3RpdmUgPSB0cnVlCiAgQU5EIHBhdGllbnRfYWRkcmVzc192aWV3LmNpdHkgPSA6Y2l0eQpPUkRFUiBCWSBwYXRpZW50X3ZpZXcuaWQsIHBhdGllbnRfYWRkcmVzc192aWV3LnVwZGF0ZWRfYXQgREVTQywgcGF0aWVudF9hZGRyZXNzX3ZpZXcuYWRkcmVzc19pZCBERVND"
}
]
}