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 |
<Library xmlns="http://hl7.org/fhir">
<id value="UniquePatientAddressesQuery"/>
<meta>
<profile value="https://sql-on-fhir.org/ig/StructureDefinition/SQLQuery"/>
</meta>
<text>
<status value="generated"/>
<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
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
)
</code></pre><p><code>application/sql;dialect=sql-2</code></p><pre><code>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
);
</code></pre></div>
</text>
<url
value="https://sql-on-fhir.org/ig/Library/UniquePatientAddressesQuery"/>
<version value="2.1.0-pre"/>
<name value="UniquePatientAddressesQuery"/>
<status value="active"/>
<type>
<coding>
<system
value="https://sql-on-fhir.org/ig/CodeSystem/LibraryTypesCodes"/>
<code value="sql-query"/>
</coding>
</type>
<date value="2025-07-31T21:26:38+00:00"/>
<publisher value="SQL on FHIR Working Group"/>
<contact>
<name value="SQL on FHIR Working Group"/>
<telecom>
<system value="url"/>
<value value="https://sql-on-fhir.org"/>
</telecom>
</contact>
<description
value="This is an example of a query library that has a few dialects:
**application/sql**
```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**
```sql
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
);
```"/>
<jurisdiction>
<coding>
<system value="http://unstats.un.org/unsd/methods/m49/m49.htm"/>
<code value="001"/>
<display value="World"/>
</coding>
</jurisdiction>
<relatedArtifact>
<type value="depends-on"/>
<resource
value="https://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition/PatientDemographics"/>
</relatedArtifact>
<relatedArtifact>
<type value="depends-on"/>
<resource
value="https://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition/PatientAddresses"/>
</relatedArtifact>
<content>
<contentType value="application/sql"/>
<data
value="LS0gU3RhbmRhcmQgU1FMCldJVEggUmFua2VkQWRkcmVzc2VzIEFTICgKICAgIFNFTEVDVCAKICAgICAgICBwZC4qLAogICAgICAgIHBhLiosCiAgICAgICAgUk9XX05VTUJFUigpIE9WRVIgKFBBUlRJVElPTiBCWSBwZC5wYXRpZW50X2lkIE9SREVSIEJZIHBhLmFkZHJlc3NfaWQpIEFTIGFkZHJlc3NfcmFuawogICAgRlJPTSAKICAgICAgICBwYXRpZW50X2RlbW9ncmFwaGljcyBwZAogICAgSk9JTiAKICAgICAgICBwYXRpZW50X2FkZHJlc3NlcyBwYSBPTiBwZC5wYXRpZW50X2lkID0gcGEucGF0aWVudF9pZAogICAgV0hFUkUgCiAgICAgICAgcGQuYWdlID4gMTgKICAgICAgICBBTkQgcGEuY2l0eSA9IE5ldyBZb3JrCikKCg=="/>
</content>
<content>
<contentType value="application/sql;dialect=sql-2"/>
<data
value="U0VMRUNUIHBkLiosIHBhLioKRlJPTSBwYXRpZW50X2RlbW9ncmFwaGljcyBwZApKT0lOIHBhdGllbnRfYWRkcmVzc2VzIHBhIE9OIHBkLnBhdGllbnRfaWQgPSBwYS5wYXRpZW50X2lkCldIRVJFIHBkLmFnZSA+IDE4CiAgQU5EIHBhLmNpdHkgPSBOZXcgWW9yawogIEFORCBwYS5hZGRyZXNzX2lkID0gKAogICAgICBTRUxFQ1QgTUlOKGFkZHJlc3NfaWQpCiAgICAgIEZST00gcGF0aWVudF9hZGRyZXNzZXMKICAgICAgV0hFUkUgcGF0aWVudF9pZCA9IHBkLnBhdGllbnRfaWQgQU5EIGNpdHkgPSBOZXcgWW9yawogICk7Cg=="/>
</content>
</Library>