quote:

---

Originally posted by stevenandler

I am trying to create a select query that will pull the latest record for each patient only. For instance, if the table has the following rows:

PATIENT_ID MRN LASTNAME FIRSTNAME
1234 6767 BROWN CHARLIE
2345 6767 BROWN CHARLIE
3456 6767 BROWN CHARLIE


I would like my select statement to return only the third row. I don't think the Distint Key word can work in this scenario because there are other columns with different date and time stamps for each row.

---

select yt.*
from   (
       select patient_id, max(dt) as dt
       from yourTable
       ) d
join   youTable yt
       on yt.patient_id = d.patient_id
       and yt.dt = d.dt