SQL query most recent value with conditions

Question

Another attempt, I wrote a piss poor question the first time. I hope this is better.

I have 2 tables - Patient and clinicalparameterh:

PATIENT
ID  Last    First
190 Smith   Frank
191 Smith   Flo


CLINICALPARAMETERH
SBP DBP Datetime            PatientID
124 86  2020-07-13 13:49:05 190
144 86  2020-08-13 13:49:05 190
134 96  2020-08-13 13:49:05 190
120 89  2009-07-13 13:49:05 190
132 76  2009-07-13 13:49:05 190
122 76  2020-07-13 13:49:05 191

I need to see

ID Last First

I would like to see this only if 1) the SBP is <140 and 2) DBP is <90 and 3) these are the most recent readings done in the last year.

So it should NOT make the list if it is from >1 year ago, or is not the most recent reading, or the SBPis >139 or the DBPis >89. (so from this example it would show Flo's name but not Frank, but cause his most recent is out of range)

Any help would be appreciated, thank you. Hopefully I explained what I was looking for.

Answer 1

Well, you can use a correlated subquery to get the most recent date. The rest is filtering. In standard SQL, you can do this as:

select p.*, cph.*
from patient p join
     CLINICALPARAMETERH cph
     on p.id = cph.patientid
where pch.datetime = (select max(cph2.datetime)
                      from CLINICALPARAMETERH cph2
                      where cph2.patientId = cph.patientId
                     ) and
      pch.datetime > current_date - interval '1 year' and
      pch.SBP < 140 and
      pch.DBP < 90;

Date/time functions are notoriously dependent on the database, so the exact syntax might vary in the database you are actually using.