SQL select max(date) and corresponding value [dupl

问题:

Possible Duplicate:
How to get the record of a table who contains the maximum 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            

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?

回答1:

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

回答2:

Ah yes, that is how it is intended in SQL. You get the Max of every column seperately. It seems like you want to return values from the row with the max date, so you have to select the row with the max date. I prefer to do this with a subselect, as the queries keep compact easy to read.

SELECT TrainingID, CompletedDate, Notes
FROM HR_EmployeeTrainings ET 
WHERE (ET.AvantiRecID IS NULL OR ET.AvantiRecID = @avantiRecID) 
AND CompletedDate in 
   (Select Max(CompletedDate) from HR_EmployeeTrainings B
    where B.TrainingID = ET.TrainingID)

If you also want to match by AntiRecID you should include that in the subselect as well.

回答3:

Each MAX function is evaluated individually. So MAX(CompletedDate) will return the value of the latest CompletedDate column and MAX(Notes) will return the maximum (i.e. alphabeticaly highest) value.

You need to structure your query differently to get what you want. This question had actually already been asked and answered several times, so I won't repeat it:

How to find the record in a table that contains the maximum value?

Finding the record with maximum value in SQL

回答4:

There's no easy way to do this, but something like this will work:

SELECT ET.TrainingID, 
  ET.CompletedDate, 
  ET.Notes
FROM 
HR_EmployeeTrainings ET
inner join
(
  select TrainingID, Max(CompletedDate) as CompletedDate
  FROM HR_EmployeeTrainings
  WHERE (ET.AvantiRecID IS NULL OR ET.AvantiRecID = @avantiRecID)
  GROUP BY AvantiRecID, TrainingID  
) ET2 
  on ET.TrainingID = ET2.TrainingID
  and ET.CompletedDate = ET2.CompletedDate