Link to home
Start Free TrialLog in
Avatar of Wonderwall
Wonderwall

asked on

Get the earliest record in a table for each patient

Aloha I need to grab the earliest visit of a patient when they were flagged as a smoker.

the fields I have are MRN, Contact_Date and Status. I am having a brain cramp at the moment and drawing a blank. :-) mahalo

Avatar of Aneesh
Aneesh
Flag of Canada image

select mrn, max(contact_date) contactDate
from tableName
where status ='smoker'
group by mrn
Avatar of Wonderwall
Wonderwall

ASKER

Sorry, forgot to add I need the status returned also so I can't use the group by  
Not sure why you said that

select mrn, max(contact_date) contactDate ,status ='smoker'
from tableName
where status ='smoker'
group by mrn

or

;with cte as (
select mrn, contact_date, status, rn = row_number() over (partition by status order by contact_date desc )
from tableName
)
select  mrn, contact_date, status from cte where rn = 1
select *
  from (
select *,row_number() over (partition by mrn order by contact_date desc) rn
  from your_table
 where status = 'smoker') t1
 where rn = 1

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
how do we know they are a smoker?