Retrieves each patient's most recent address in a given city. Includes two dialects for the same logical query.

Standard 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:

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