如何获得最高级别的严重性级别。 [英] How to get severity level with max rank.

问题描述

我有以下查询返回样本代码和与样本相​​关联的最大等级，但我需要该表（prod.AssessmentDataChecks）的严重性级别，其等级为该样本的最高等级。



**查询**

I have following query which returns sample code and maximum rank which is associated to sample, but i need severity level from that table(prod.AssessmentDataChecks) whose rank is maximum for that sample.

**Query**

select S.sampleCode,max(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
order by S.sampleCode desc

我的尝试：



我什么都不知道。任何帮助将不胜感激

What I have tried:

I didn't get any idea. Any help will be appreciated

推荐答案

   SELECT DISTINCT
             S.sampleCode,
             asd.severityLevelRank,
max(asd.severityLevelRank)
OVER(PARTITION BY S.sampleCode ORDER BY S.sampleCode DESC) as MAX_SLR
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

这篇关于如何获得最高级别的严重性级别。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！