SQL on FHIR
3.0.0-ballot - STU 3 Ballot International flag

SQL on FHIR, published by HL7 International / FHIR Infrastructure. This guide is not an authorized publication; it is the continuous build for version 3.0.0-ballot built by the FHIR (HL7® FHIR® Standard) CI Build. This version is based on the current content of https://github.com/HL7/sql-on-fhir/ and changes regularly. See the Directory of published versions

: Unique Patient Addresses

Page standards status: Informative

Raw xml | Download


<Library xmlns="http://hl7.org/fhir">
  <id value="UniquePatientAddressesQuery"/>
  <meta>
    <profile
             value="http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/SQLQuery"/>
  </meta>
  <language value="en"/>
  <text>
    <status value="extensions"/>
    <div xmlns="http://www.w3.org/1999/xhtml">
  <table class="narrative-table">
    <tbody>
<tr>


<th colspan="2" scope="row" class="row-header">Metadata</th>


</tr>

<tr>


<th scope="row" class="row-header">Title</th>



<td class="content-container">Unique Patient Addresses</td>


</tr>



<tr>


<th scope="row" class="row-header">Version</th>



<td class="content-container">3.0.0-ballot</td>


</tr>







  
<tr>


<th scope="row" class="row-header">Identifier</th>



<td class="content-container">urn:oid:2.16.840.1.113883.4.642.40.77.28.5</td>


</tr>










<tr>


<th scope="row" class="row-header">Jurisdiction</th>



<td class="content-container">
    World


</td>


</tr>








<tr>


<th scope="row" class="row-header">Steward (Publisher)</th>



<td class="content-container">HL7 International / FHIR Infrastructure</td>


</tr>



<tr>


<th scope="row" class="row-header">Steward Contact</th>



<td class="content-container">HL7 International / FHIR Infrastructure</td>


</tr>







<tr>


<th scope="row" class="row-header">Description</th>



<td class="content-container"><div><p>Retrieves each patient's most recent address in a given city. Includes two
dialects for the same logical query.</p>
<p><strong>Standard SQL:</strong></p>
<pre><code class="language-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
</code></pre>
<p><strong>PostgreSQL dialect:</strong></p>
<pre><code class="language-sql">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
</code></pre>
</div></td>


</tr>






<tr>


<th scope="row" class="row-header">Type</th>



<td class="content-container">
    sql-query from <a href="CodeSystem-LibraryTypesCodes.html">http://hl7.org/fhir/uv/sql-on-fhir/CodeSystem/LibraryTypesCodes</a> 


</td>


</tr>



<tr>
  
  
  
  
  

<th scope="row" class="row-header">Dependency</th>


  
  
  <td class="content-container">
    
    
    
    
    
    <em>Resource</em>: <code>https://example.org/ViewDefinition/patient_view</code>
    <br/>
    <em>Canonical URL</em>: <tt>https://example.org/ViewDefinition/patient_view</tt>
    
    
    
  </td>
</tr>

<tr>
  
  
  
  
  

<th scope="row" class="row-header">Dependency</th>


  
  
  <td class="content-container">
    
    
    
    
    
    <em>Resource</em>: <code>https://example.org/ViewDefinition/patient_address_view</code>
    <br/>
    <em>Canonical URL</em>: <tt>https://example.org/ViewDefinition/patient_address_view</tt>
    
    
    
  </td>
</tr>








<tr>


<th colspan="2" scope="row" class="row-header"><a name="parameters"> </a>Parameters</th>


</tr>

<tr>
  

<th scope="row" class="row-header"/>


  <td class="content-container">
    <table>
      <thead>
        <tr>
          <td><em>Name</em></td>
          <td><em>Use</em></td>
          <td><em>Card.</em></td>
          <td><em>Type</em></td>
          <td><em>Documentation</em></td>
        </tr>
      </thead>
      <tbody>
      
        <tr>
          <td>city</td>
          <td>In</td>
          <td>0..1</td>
          <td>string</td>
          <td>City to filter addresses</td>
        </tr>
      
      </tbody>
    </table>
  </td>
</tr>







  <tr>


<th colspan="2" scope="row" class="row-header">Library Content</th>


</tr>
  
    
    <tr>
      <th scope="row" class="row-header">application/sqlContent</th>
      <td>
        <pre style="border: none;" class="content-container highlight language-cql"><code>Encoded data </code></pre>
      </td>
    </tr>
    
  
    
    <tr>
      <th scope="row" class="row-header">application/sql;dialect=postgresqlContent</th>
      <td>
        <pre style="border: none;" class="content-container highlight language-cql"><code>Encoded data </code></pre>
      </td>
    </tr>
    
  

<tr>
  <th colspan="2" scope="row" class="row-header">Generated using version 0.5.4 of the sample-content-ig Liquid templates</th>
</tr>

    </tbody>
  </table>
</div>
  </text>
  <url
       value="http://hl7.org/fhir/uv/sql-on-fhir/Library/UniquePatientAddressesQuery"/>
  <identifier>
    <system value="urn:ietf:rfc:3986"/>
    <value value="urn:oid:2.16.840.1.113883.4.642.40.77.28.5"/>
  </identifier>
  <version value="3.0.0-ballot"/>
  <name value="UniquePatientAddressesQuery"/>
  <title value="Unique Patient Addresses"/>
  <status value="active"/>
  <type>
    <coding>
      <system
              value="http://hl7.org/fhir/uv/sql-on-fhir/CodeSystem/LibraryTypesCodes"/>
      <code value="sql-query"/>
    </coding>
  </type>
  <date value="2026-08-13T15:42:34+00:00"/>
  <publisher value="HL7 International / FHIR Infrastructure"/>
  <contact>
    <name value="HL7 International / FHIR Infrastructure"/>
    <telecom>
      <system value="url"/>
      <value value="http://www.hl7.org/Special/committees/fiwg"/>
    </telecom>
  </contact>
  <description
               value="Retrieves each patient's most recent address in a given city. Includes two
dialects for the same logical query.

**Standard SQL:**
```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:**
```sql
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
```"/>
  <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"/>
    <label value="patient_view"/>
    <resource value="https://example.org/ViewDefinition/patient_view"/>
  </relatedArtifact>
  <relatedArtifact>
    <type value="depends-on"/>
    <label value="patient_address_view"/>
    <resource
              value="https://example.org/ViewDefinition/patient_address_view"/>
  </relatedArtifact>
  <parameter>
    <name value="city"/>
    <use value="in"/>
    <documentation value="City to filter addresses"/>
    <type value="string"/>
  </parameter>
  <content>
    <extension
               url="http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/sql-text">
      <valueString
                   value="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"/>
    </extension>
    <contentType value="application/sql"/>
    <data
          value="V0lUSCByYW5rZWRfYWRkcmVzc2VzIEFTICgKICBTRUxFQ1QKICAgIHBhdGllbnRfdmlldy5pZCBBUyBwYXRpZW50X2lkLAogICAgcGF0aWVudF92aWV3Lm5hbWUsCiAgICBwYXRpZW50X2FkZHJlc3Nfdmlldy5hZGRyZXNzX2xpbmUxLAogICAgcGF0aWVudF9hZGRyZXNzX3ZpZXcuY2l0eSwKICAgIHBhdGllbnRfYWRkcmVzc192aWV3LnN0YXRlLAogICAgcGF0aWVudF9hZGRyZXNzX3ZpZXcucG9zdGFsX2NvZGUsCiAgICBwYXRpZW50X2FkZHJlc3Nfdmlldy51cGRhdGVkX2F0LAogICAgUk9XX05VTUJFUigpIE9WRVIgKAogICAgICBQQVJUSVRJT04gQlkgcGF0aWVudF92aWV3LmlkCiAgICAgIE9SREVSIEJZIHBhdGllbnRfYWRkcmVzc192aWV3LnVwZGF0ZWRfYXQgREVTQywgcGF0aWVudF9hZGRyZXNzX3ZpZXcuYWRkcmVzc19pZCBERVNDCiAgICApIEFTIGFkZHJlc3NfcmFuawogIEZST00gcGF0aWVudF92aWV3CiAgSk9JTiBwYXRpZW50X2FkZHJlc3NfdmlldwogICAgT04gcGF0aWVudF92aWV3LmlkID0gcGF0aWVudF9hZGRyZXNzX3ZpZXcucGF0aWVudF9pZAogIFdIRVJFIHBhdGllbnRfdmlldy5hY3RpdmUgPSB0cnVlCiAgICBBTkQgcGF0aWVudF9hZGRyZXNzX3ZpZXcuY2l0eSA9IDpjaXR5CikKU0VMRUNUCiAgcGF0aWVudF9pZCwKICBuYW1lLAogIGFkZHJlc3NfbGluZTEsCiAgY2l0eSwKICBzdGF0ZSwKICBwb3N0YWxfY29kZQpGUk9NIHJhbmtlZF9hZGRyZXNzZXMKV0hFUkUgYWRkcmVzc19yYW5rID0gMQ=="/>
  </content>
  <content>
    <extension
               url="http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/sql-text">
      <valueString
                   value="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"/>
    </extension>
    <contentType value="application/sql;dialect=postgresql"/>
    <data
          value="U0VMRUNUIERJU1RJTkNUIE9OIChwYXRpZW50X3ZpZXcuaWQpCiAgcGF0aWVudF92aWV3LmlkIEFTIHBhdGllbnRfaWQsCiAgcGF0aWVudF92aWV3Lm5hbWUsCiAgcGF0aWVudF9hZGRyZXNzX3ZpZXcuYWRkcmVzc19saW5lMSwKICBwYXRpZW50X2FkZHJlc3Nfdmlldy5jaXR5LAogIHBhdGllbnRfYWRkcmVzc192aWV3LnN0YXRlLAogIHBhdGllbnRfYWRkcmVzc192aWV3LnBvc3RhbF9jb2RlCkZST00gcGF0aWVudF92aWV3CkpPSU4gcGF0aWVudF9hZGRyZXNzX3ZpZXcKICBPTiBwYXRpZW50X3ZpZXcuaWQgPSBwYXRpZW50X2FkZHJlc3Nfdmlldy5wYXRpZW50X2lkCldIRVJFIHBhdGllbnRfdmlldy5hY3RpdmUgPSB0cnVlCiAgQU5EIHBhdGllbnRfYWRkcmVzc192aWV3LmNpdHkgPSA6Y2l0eQpPUkRFUiBCWSBwYXRpZW50X3ZpZXcuaWQsIHBhdGllbnRfYWRkcmVzc192aWV3LnVwZGF0ZWRfYXQgREVTQywgcGF0aWVudF9hZGRyZXNzX3ZpZXcuYWRkcmVzc19pZCBERVND"/>
  </content>
</Library>