SQL 选择 max(date) 和对应的值 [英] SQL select max(date) and corresponding value

问题描述

可能的重复:
如何获取记录包含最大值的表?

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

I've got an aggregate query like the following:

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) 记录中的 notes 字段.我该怎么做?

Which is working, and returns correct data most of the time, but I noticed a problem. The Notes field which gets returned will not necessarily match the record that the max(completedDate) is from. Instead it will be the one with the longest string? Or the one with the highest ASCII value? What does SQL Server do in the event of a tie between two records? I'm not even sure. What I want to get is the notes field from the max(completedDate) record. How should I got about doing this?

推荐答案

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

You can use a subquery. The subquery will get the Max(CompletedDate). You then take this value and join on your table again to retrieve the note associate with that date:

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

这篇关于SQL 选择 max(date) 和对应的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！