How to query by recent date and value in SQL?

Question

I have a table with three columns:

• patient_id
• obs_date
• weight_val

patient_id stores patient identification #, weight_val stores a weight value, and obs_date stores the date when the weight reading was taken. So, a patient can have many different weight readings at different dates.

How do you write a query for:

select all patients whose last weight reading is > 120?

Answer 1

Off the top of my head, please pardon any syntax errors

Select tbl.*
From le_table tbl
Inner Join (
    Select patient_id, Max(obs_date) obs_date
    From le_table
    Group By patient_id
) t2
On tbl.patient_id = t2.patient_id
    and tbl.obs_date = t2.obs_date
Where tbl.weight_val > 120

Basically you need to join to the subset containing the maximum observation date per patient (on patient ID and obs date). Once you have only the most recent reading per patient, you can check for weight > 120