Comments by alicashah (Top 5 by date)

Thank you solution 2 worked.

Thank you once again!!!

Thank you !!!

One more thing what do I do if I have to do this for all patient who are in bed?
if I used the below code then I get only 4 rows back. It worked for that patient id '11' but it didnot give me the results for all patient. also I am using SQL SERVER 2008. Thank you.

WITH cteRankedData As
(
SELECT
a.PatientAccountID,
e.FindingAbbr,
e.Value,
e.CreationTime,
ROW_NUMBER() OVER (PARTITION BY e.FindingAbbr ORDER BY e.CreationTime DESC) As RN
FROM
PatientVisitInfo a with (nolock)
INNER JOIN Assessment d with (nolock)
ON a.PatientVisit_oid = d.PatientVisit_oid
INNER JOIN Observation e with (nolock)
ON e.AssessmentID = d.AssessmentID
WHERE

a.VisitTypeCode='IP'
AND
a.VisitEndDateTime is null
AND
e.Value <> ''
AND
e.FindingAbbr IN ('A_IV1 Site', 'A_IV2 Site', 'A_IV3 Site', 'A_IV4 Site')
)
SELECT
PatientAccountID,
FindingAbbr,
Value,
CreationTime As LatestCreationTime
FROM
cteRankedData
WHERE
RN = 1
ORDER BY
PatientAccountID,
FindingAbbr,
Value
;

Thank you!!!