如何在SQL SERVER中获取最新值和最新日期 [英] How to get the latest value and latest date in SQL SERVER

问题描述

select PatientAccountID,FindingAbbr,Value,LatestCreationTime from
(SELECT
   a.PatientAccountID
   ,e.FindingAbbr
   ,e.Value
   ,max(e.CreationTime) as LatestCreationTime
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.PatientAccountID= '11'
 AND 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')
 Group BY a.PatientAccountID
   ,e.FindingAbbr,e.Value) as ab
Order by PatientAccountID,  FindingAbbr,Value

Results I am getting:

PatientAccountID	FindingAbbr	Value	LatestCreationTime
34990226	A_IV1 Site	L Forearm	2014-11-11 08:01:00
34990226	A_IV1 Site	R Forearm	2014-10-31 20:57:00
34990226	A_IV2 Site	R Antecubital	2014-10-31 20:57:00
34990226	A_IV2 Site	R Forearm	2014-11-11 08:01:00
34990226	A_IV3 Site	R Forearm	2014-11-11 08:01:00
34990226	A_IV3 Site	R Upper Arm	2014-10-31 20:57:00
34990226	A_IV4 Site	L Forearm	2014-10-31 20:57:00
34990226	A_IV4 Site	R Antecubital	2014-11-11 08:01:00

Results I am looking for is for each FindingAbbr the latest value and the latest date (only one to be displayed):

PatientAccountID	FindingAbbr	Value	       LatestCreationTime
34990226	A_IV1 Site	L Forearm	       2014-11-11 08:01:00
34990226	A_IV2 Site	R Forearm	       2014-11-11 08:01:00
34990226	A_IV3 Site	R Forearm	       2014-11-11 08:01:00
34990226	A_IV4 Site	R Antecubital	       2014-11-11 08:01:00

推荐答案

您尚未指定您正在使用的DBMS。假设Microsoft SQL Server 2005或更高版本， ROW_NUMBER 排名功能 [ ^ ]应该work：

You haven't specified which DBMS you're using. Assuming Microsoft SQL Server 2005 or later, the ROW_NUMBER ranking function[^] should work:

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.PatientAccountID= '11'
    AND 
        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
;

试试这个

Try this

select PatientAccountID,FindingAbbr,Value,LatestCreationTime from
(
SELECT
   a.PatientAccountID
   ,e.FindingAbbr
   ,e.Value
   ,e.CreationTime as LatestCreationTime
   ,ROW_NUMBER() Over (Partition by PatientAccountID Order by CreationTime desc) As OrderID
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.PatientAccountID= '11'
 AND 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')
 Group BY a.PatientAccountID
   ,e.FindingAbbr,e.Value

   ) as ab
   Where OrderID=1
Order by PatientAccountID,  FindingAbbr,Value

这篇关于如何在SQL SERVER中获取最新值和最新日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！