Selecting Grouped Rows With Max Value

[Moebius01]

Ok, considering the approaching holiday and severe braincramping as a side effect, I'm stuck on what I thought would be a very simple query.

The table stores EmployeeID, EvalID, CompletionDate. I need to pull the record for each employee's latest completion date. i.e. if each employee has 4 records, I need the record for each employee with the lastest CompletionDate.

 

[RiverGuy]

Does CompletionDate have a time component stored? Why I ask this is in the case where you might be storing just the date with midnight, you could have non distinct "MAX" CompletionDates if they were recorded on the same day. Anyways, assuming the time portion exists:

SELECT a.*
FROM YourTable a

INNER JOIN

(SELECT EmployeeID, MAX(CompletionDate) AS MaxDate
FROM YourTable
GROUP BY EmployeeID) b

ON a.EmployeeID = b.EmployeeID AND a.CompletionDate = b.MaxDate

 

[tlbroadbent]

An alternative.

SELECT EmployeeID, EvalID, CompletionDate
FROM tablename AS t
WHERE CompletionDate =
( SELECT MAX(CompletionDate)
  FROM tablename
  WHERE EmployeeID = t.EmployeeID)

Make sure you have an index on EmployeeID. For best performance index EmployeeID, CompletionDate. You should compare this query with the query from RiverGuy to detrermine which perfomrmas best in your environment.

Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)