Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 820
  • Last Modified:

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

0
Wonderwall
Asked:
Wonderwall
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
select mrn, max(contact_date) contactDate
from tableName
where status ='smoker'
group by mrn
0
 
WonderwallAuthor Commented:
Sorry, forgot to add I need the status returned also so I can't use the group by  
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
SharathData EngineerCommented:
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

0
 
HainKurtSr. System AnalystCommented:
I guess "earliest date" means we need to order by ASC
select * from (
select t.*, row_number() over (partition by mrn order by contact_date ASC) rn
  from your_table
 where Smoker = 'Y') x
 where rn = 1

Open in new window

0
 
LowfatspreadCommented:
how do we know they are a smoker?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now