May 16, 2012 at 4:38 pm
Hello,
I have two tables.
Table 1 has patient.ID and patient.lastname. Patient.ID is the unique identifier: each patient is assigned a unique patient.ID.
Table 2 has patient.id, test.date, and test.value.
Each patient can have more than one test.date and test.value.
I want only the first test.date for each unique patient.ID and combine the test information with patient.lastname in the query results. I understand JOIN statements but I am stuck on how to select only the first test.date.
I'd truly appreciate any advice!
Thanks,
Danielle
May 16, 2012 at 4:57 pm
You might want to look up TOP and ORDER BY.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 16, 2012 at 4:57 pm
What you want to do is to utilize ROW_NUMBER() on table2 , join with table1 and pickk up only the values whre ROW_NUMBER() = 1
May 16, 2012 at 4:59 pm
Thanks to you both. I did look up Top and Order by, but I couldn't figure out how to pick them out for each unique patient ID. I will give your suggestions a try. Thank you again!
May 16, 2012 at 5:11 pm
Like this
My method
declare @Patients table
( PatientID INT)
declare @Tests table
( PatientID INT, DataofTest DATETIME , TestValue VARCHAR(20) )
insert into @Patients (PatientID)
select 1
union all select 2
union all select 3
insert into @Tests (PatientID , DataofTest , TestValue)
select 1 , '2012-05-16 16:05:10.667' , 'Cardio Pass'
union all select 1 , '2012-05-17 16:05:10.667' , 'Angio Pass'
union all select 1 , '2012-05-18 16:05:10.667' , 'Physio Fail'
union all select 2 , '2012-06-10 16:05:10.667' , 'Cardio Pass'
union all select 2 , '2012-03-30 16:05:10.667' , 'Angio Pass'
union all select 3 , '2012-05-18 16:05:10.667' , 'Physio Fail'
; with cte as
(
select t.PatientID , t.TestValue , t.DataofTest
, RN = ROW_NUMBER() over(partition by t.PatientID order by t.DataofTest )
from @Tests t
)
select p.PatientID , t.TestValue , t.DataofTest
from @Patients P
join cte T
on P.PatientID = t.PatientID
and t.RN =1
SQLRNNR's method:
select p.PatientID , CrsApp.DataofTest , CrsApp.TestValue
from @Patients P
cross apply
( select top 1 t.DataofTest , t.TestValue
from @Tests t
where t.PatientID = p.PatientID
order by t.DataofTest asc
) CrsApp
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply