SQL Query: how to select records, but if parent record exists, select the most recent one
I have a complex query that I need to figure out, but I don't know enough about writing queries and subqueries.
Problem: I need to get unique patient records, but if the record has a null master_patient_id, I need to subquery or join that master_patient table and query for the most recent (created_at desc limit 1) child patient of that host patient.
The reason for this is that our system will create a new patient record for the same patient if they are transferred to the same object. After creating a second record for this patient, we also create a master_patient record to link the two patient records to it so the system can know that they are the same patient.
Now I need to show a list of non-duplicated patients. So I need to get a query that will get patients from a patient record, but query the master_patient table and only get the last patient associated with his master_patient.
Patient table has: id, name, master_patient_id and patient belongs to _master_patient but does not need to be present.
The main patients table only has id and has_many patients.
Desired Outcomes: Must be unique patient accounts, but the only way to find out if patients are unique among themselves is to query the master_patient table to see if any patients belong to it and then just get the last patient (child from master_patient) ...
I can't base my query on master_patient because that doesn't exist for patients who only have 1 record. Should I be using some kind of join or subquery?
Update: Thanks to @ τεκ, I was able to configure his query to work in Postgres:
Update 2: Another tiny query hint to make it shorter and fix the empty id being returned:
SELECT MAX(patients.id) as id, *
FROM "patients"
JOIN (
SELECT MAX(created_at) AS created_at,
patient_master_id,
COALESCE(patient_master_id, id) pm_id
FROM patients
GROUP BY patient_master_id,
COALESCE(patient_master_id, id)
) s
ON (s.pm_id = patients.id or s.patient_master_id = patients.patient_master_id)
AND s.created_at = patients.created_at
GROUP BY patients.id, s.created_at, s.patient_master_id, s.pm_id
source to share
select max(id) as id from patient p
join (select
max(created_at) as created_at,
master_patient_id,
case when master_patient_id is null then id else null end as id
from patient
group by master_patient_id, case when master_patient_id is null then id else null end
) s on (s.id = p.id or s.master_patient_id = p.master_patient_id) and s.created_at = p.created_at
Probably an easier, postgrey way to do it, but I don't know postgres. In T-SQL, this is cross apply
.
source to share