database - Sql get the latest row in a table by date - double select vs order by

Question

Have a query that uses double select (with select max) to fetch the row with the latest 'calculation_time' column among multiple rows which can have the same 'patient_set_id'. If there are multiple rows with the same 'patient_set_id', only the row with the latest 'calculation_time' should be retrieved. Calculation time is a date. So far I've tried this but I'm not really sure if there is any better way for this, maybe using ORDER BY. But I'm very new to sql and need to know which one would be the fastest and more appropriate?

SELECT median from diagnostic_risk_stats WHERE 
      calculation_time=(SELECT MAX(calculation_time) FROM diagnostic_risk_stats WHERE 
      patient_set_id = UNHEX(REPLACE('5a9dbfca-74d6-471a-af27-31beb4b53bb2', "-","")));

Answer 1

You can use not exists as follows:

SELECT median 
 from diagnostic_risk_stats t
 WHERE not exists
       (Select 1 from diagnostic_risk_stats tt
         Where t.patient_set_id = tt.patient_set_id
           And tt.calculation_time > t.calculation_time)
  And t.patient_set_id = UNHEX(REPLACE('5a9dbfca-74d6-471a-af27-31beb4b53bb2', "-",""));