GROUP BY中的T-SQL额外列。如何包含在结果中但从分组中排除

I have the following SQL

我有以下SQL

SELECT     CNumber, MAX(FormSubmitted), Score
FROM         dbo.vw_MSA
WHERE     (Score IS NOT NULL)
GROUP BY CNumber, Score

Results:

CNumber FormSubmitted                   Score
0011509 2010-09-13 10:22:46.763         11
0016239 2010-09-28 13:53:58.557         8*
0016239 2010-10-12 11:52:33.150         12
0018792 2010-11-02 13:17:37.437         3
0051089 2010-10-07 11:38:29.257         13
0051091 2010-10-01 12:01:30.450         0
0067987 2010-09-29 11:06:48.490         7
0079351 2010-09-30 09:49:10.747         34
0833933 2010-09-30 12:54:19.013         6
0848185 2010-10-07 12:08:42.087         6
5010590 2010-11-01 09:54:14.790         6*
5010590 2010-11-16 15:23:50.350         12

I'm sure I should be able to sort this easily but for whatever reason I'm struggling atm. I need Score included in the results but having this in the GROUP BY inevitably returns more than just the record with the latest FormSubmitted date. Can anyone help me out in updating this query so I will get the Score in the results but only those records that are the maximum FormSubmitted date i.e exclude those marked *?

我相信我应该能够轻松地对此进行排序,但无论出于何种原因我都在努力解决这个问题。我需要在结果中包含得分,但在GROUP BY中使用得分不可避免地返回的不仅仅是具有最新FormSubmitted日期的记录。任何人都可以帮助我更新这个查询,所以我会得到结果中的分数,但只有那些最大FormSubmitted日期的记录,即排除那些标记*?

2 个解决方案

#1

SELECT     A.CNumber, A.FormSubmitted, A.Score
FROM         dbo.vw_MSA A
INNER JOIN 
(SELECT CNumber, MAX(FormSubmitted) AS MaxSubmitted 
FROM dbo.vw_MSA
WHERE     (Score IS NOT NULL)
GROUP BY CNumber) AS B
ON A.CNumber = B.CNumber AND A.FormSubmitted = B.MaxSubmitted

#2

Assuming that (CNumber, FormSubmitted) is guaranteed to be unique:

假设(CNumber,FormSubmitted)保证是唯一的:

SELECT T1.CNumber, T1.FormSubmitted, T1.Score
FROM dbo.vw_MSA T1
JOIN
(
    SELECT CNumber, MAX(FormSubmitted) AS MaxFormSubmitted
    FROM dbo.vw_MSA
    WHERE Score IS NOT NULL
    GROUP BY CNumber
) T2
ON T1.CNumber = T2.CNumber
AND T1.CNumber = T2.MaxFormSubmitted