SQL - select FIRST record from a sub-query for each item (patient or customer) 1

[Baesucks]

Every once in a while I need to do this type of query and I don't seem to know the best way to do it.

I have a table with fields like these...
PatientID
VisitDate
InsuranceType
InsuranceCompanyName
InsurancePlanName

I want to end up with a single record for each PatientID that contains the information from the most recent date. I can easily create the initial query for putting the items in the order I want so that the record I want to select for each patient is the first on in the result for each PatientID, but then I'm not sure how to best select only the first record from this initial query for each patient when trying to join this result set with the Patients table.

A possible added issue is that there can be more than one record in the initial source with the same PatientID and VisitDate, so I am also sorting the initial data by the InsuranceType field as well to determine which record comes first in the inital query.

There has to be a standard technique for doing this sort of thing...

 

[gmmastros]

Does this work? If so, and you want me to explain it, let me know.

; With Data As
(
  Select *, 
         Row_Number() Over (Partition By PatientId, InsuranceType Order By VisitDate DESC) As RowId
  From   [!]YourTableNameHere[/!]
)
Select *
From   Data
Where  RowId = 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sabinUE]

select distinct T.PatientID
from Table as T
cross apply
(select top 1
PatientID,
VisitDate,
InsuranceType
from Table TCA
where T.patientID=TCA.patientID
order by VisitDate desc,InsuranceType
)CA

Not tested.

the idee is to use cross apply

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin