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
Active as of 2025-07-31 |
{
"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><code>https://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition/PatientDemographics</code></td></tr><tr><td>Depends On</td><td><code>https://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition/PatientAddresses</code></td></tr></table><h2>Contents</h2><p><code>application/sql</code></p><pre><code>-- Standard SQL\nWITH RankedAddresses AS (\n SELECT \n pd.*,\n pa.*,\n ROW_NUMBER() OVER (PARTITION BY pd.patient_id ORDER BY pa.address_id) AS address_rank\n FROM \n patient_demographics pd\n JOIN \n patient_addresses pa ON pd.patient_id = pa.patient_id\n WHERE \n pd.age > 18\n AND pa.city = New York\n)\n\n</code></pre><p><code>application/sql;dialect=sql-2</code></p><pre><code>SELECT pd.*, pa.*\nFROM patient_demographics pd\nJOIN patient_addresses pa ON pd.patient_id = pa.patient_id\nWHERE pd.age > 18\n AND pa.city = New York\n AND pa.address_id = (\n SELECT MIN(address_id)\n FROM patient_addresses\n WHERE patient_id = pd.patient_id AND city = New York\n );\n</code></pre></div>"
},
"url" : "https://sql-on-fhir.org/ig/Library/UniquePatientAddressesQuery",
"version" : "2.1.0-pre",
"name" : "UniquePatientAddressesQuery",
"status" : "active",
"type" : {
"coding" : [
{
"system" : "https://sql-on-fhir.org/ig/CodeSystem/LibraryTypesCodes",
"code" : "sql-query"
}
]
},
"date" : "2025-07-31T21:26:38+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" : "This is an example of a query library that has a few dialects:\n\n**application/sql**\n\n```sql\n-- Standard SQL\nWITH RankedAddresses AS (\n SELECT \n pd.*,\n pa.*,\n ROW_NUMBER() OVER (PARTITION BY pd.patient_id ORDER BY pa.address_id) AS address_rank\n FROM \n patient_demographics pd\n JOIN \n patient_addresses pa ON pd.patient_id = pa.patient_id\n WHERE \n pd.age > 18\n AND pa.city = New York\n)\n```\n\n**application/sql; dialect=sql-2**\n\n```sql\nSELECT pd.*, pa.*\nFROM patient_demographics pd\nJOIN patient_addresses pa ON pd.patient_id = pa.patient_id\nWHERE pd.age > 18\n AND pa.city = New York\n AND pa.address_id = (\n SELECT MIN(address_id)\n FROM patient_addresses\n WHERE patient_id = pd.patient_id AND city = New York\n );\n```",
"jurisdiction" : [
{
"coding" : [
{
"system" : "http://unstats.un.org/unsd/methods/m49/m49.htm",
"code" : "001",
"display" : "World"
}
]
}
],
"relatedArtifact" : [
{
"type" : "depends-on",
"resource" : "https://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition/PatientDemographics"
},
{
"type" : "depends-on",
"resource" : "https://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition/PatientAddresses"
}
],
"content" : [
{
"contentType" : "application/sql",
"data" : "LS0gU3RhbmRhcmQgU1FMCldJVEggUmFua2VkQWRkcmVzc2VzIEFTICgKICAgIFNFTEVDVCAKICAgICAgICBwZC4qLAogICAgICAgIHBhLiosCiAgICAgICAgUk9XX05VTUJFUigpIE9WRVIgKFBBUlRJVElPTiBCWSBwZC5wYXRpZW50X2lkIE9SREVSIEJZIHBhLmFkZHJlc3NfaWQpIEFTIGFkZHJlc3NfcmFuawogICAgRlJPTSAKICAgICAgICBwYXRpZW50X2RlbW9ncmFwaGljcyBwZAogICAgSk9JTiAKICAgICAgICBwYXRpZW50X2FkZHJlc3NlcyBwYSBPTiBwZC5wYXRpZW50X2lkID0gcGEucGF0aWVudF9pZAogICAgV0hFUkUgCiAgICAgICAgcGQuYWdlID4gMTgKICAgICAgICBBTkQgcGEuY2l0eSA9IE5ldyBZb3JrCikKCg=="
},
{
"contentType" : "application/sql;dialect=sql-2",
"data" : "U0VMRUNUIHBkLiosIHBhLioKRlJPTSBwYXRpZW50X2RlbW9ncmFwaGljcyBwZApKT0lOIHBhdGllbnRfYWRkcmVzc2VzIHBhIE9OIHBkLnBhdGllbnRfaWQgPSBwYS5wYXRpZW50X2lkCldIRVJFIHBkLmFnZSA+IDE4CiAgQU5EIHBhLmNpdHkgPSBOZXcgWW9yawogIEFORCBwYS5hZGRyZXNzX2lkID0gKAogICAgICBTRUxFQ1QgTUlOKGFkZHJlc3NfaWQpCiAgICAgIEZST00gcGF0aWVudF9hZGRyZXNzZXMKICAgICAgV0hFUkUgcGF0aWVudF9pZCA9IHBkLnBhdGllbnRfaWQgQU5EIGNpdHkgPSBOZXcgWW9yawogICk7Cg=="
}
]
}