SQL select max(date) 和对应的值

Question

这个问题已经有答案了:

已关闭 10 年前。

Possible Duplicate:
How to get the record of a table who contains the maximum value?

我有一个如下所示的聚合查询:

SELECT TrainingID, Max(CompletedDate) as CompletedDate, Max(Notes) as Notes     --This will only return the longest notes entry
FROM HR_EmployeeTrainings ET
WHERE (ET.AvantiRecID IS NULL OR ET.AvantiRecID = @avantiRecID)
GROUP BY AvantiRecID, TrainingID            

它正在工作，并且大多数时候返回正确的数据，但我注意到一个问题。返回的 Notes 字段不一定与 max(completedDate) 所在的记录匹配。相反，它将是具有最长字符串的那一个？或者 ASCII 值最高的那个？如果两条记录之间存在平局，SQL Server 会做什么？我什至都不确定。我想要得到的是 max(completedDate) 记录中的注释字段。我应该怎样做呢？

Answer 1

您可以使用子查询。子查询将获取Max(CompletedDate)。然后，您获取该值并再次加入您的表以检索与该日期关联的注释:

select ET1.TrainingID,
  ET1.CompletedDate,
  ET1.Notes
from HR_EmployeeTrainings ET1
inner join
(
  select Max(CompletedDate) CompletedDate, TrainingID
  from HR_EmployeeTrainings
  --where AvantiRecID IS NULL OR AvantiRecID = @avantiRecID
  group by TrainingID
) ET2
  on ET1.TrainingID = ET2.TrainingID
  and ET1.CompletedDate = ET2.CompletedDate
where ET1.AvantiRecID IS NULL OR ET1.AvantiRecID = @avantiRecID