获得记录的最高值

Question

我有下面的查询，它返回与其相关的所有严重级别的示例。

但我只需要一个等级级别最高的严重程度。

我试过什么

select S.sampleCode,asd.severityLevelRank
from prod.Samples S
left outer join prod.SampleFractions sf on sf.sampleId=S.sampleId
left outer join prod.LabRepetitions lr on 
sf.sampleFractionId=lr.sampleFractionId
left outer join prod.LabRepetitionResults lrr on 
lrr.labRepetitionId=lr.labRepetitionId
left outer join prod.AssessmentDataChecks asd on 
lrr.labRepetitionResultId=asd.labRepetitionResultId

group by S.sampleCode, asd.severityLevelRank

order by S.sampleCode desc, asd.severityLevelRank desc

我得到了什么，

​

​

但是我需要一个最高等级的样本，也就是3。

Answer 1

如果您希望按照每组样本的级别来确定最高的严重程度，那么您可以尝试使用DENSE_RANK()。请注意，在这里使用密集秩非常有用，因为它减轻了您在GROUP BY聚合期间删除的重复样例/严重性记录的需要。如果给定样本出现重复严重级别，则该级别不会增加。

SELECT
    sampleCode,
    severityLevelRank
FROM
(
    SELECT
        S.sampleCode, asd.severityLevelRank,
        DENSE_RANK() OVER (PARTITION BY S.sampleCode ORDER BY asd.severityLevelRank) dr
    FROM prod.Samples S
    LEFT JOIN prod.SampleFractions sf
        ON sf.sampleId = S.sampleId
    LEFT JOIN prod.LabRepetitions lr
        ON sf.sampleFractionId = lr.sampleFractionId
    LEFT JOIN prod.LabRepetitionResults lrr
        ON lrr.labRepetitionId = lr.labRepetitionId
    LEFT JOIN prod.AssessmentDataChecks asd
        ON lrr.labRepetitionResultId = asd.labRepetitionResultId
) t
WHERE
    dr = 1
ORDER BY
    sampleCode DESC,
    severityLevelRank DESC

Answer 2

只需替换

select S.sampleCode,asd.severityLevelRank

通过

select S.sampleCode, MAX(asd.severityLevelRank)

在选择中。

您还可以添加

WHERE asd.severityLevelRank IS NOT NULL 

下至

若要忽略带有空值的sampleCode，请执行以下操作。

Answer 3

除非我遗漏了什么，否则这是一个简单的group by和max

select S.sampleCode, max(asd.severityLevelRank) As severityLevelRank
from prod.Samples S
left outer join prod.SampleFractions sf on sf.sampleId=S.sampleId
left outer join prod.LabRepetitions lr on 
sf.sampleFractionId=lr.sampleFractionId
left outer join prod.LabRepetitionResults lrr on 
lrr.labRepetitionId=lr.labRepetitionId
left outer join prod.AssessmentDataChecks asd on 
lrr.labRepetitionResultId=asd.labRepetitionResultId

group by S.sampleCode

order by S.sampleCode desc