1

Here's the scenario. I have employees and they evaluated yearly for different categories. I need: the full row of the latest evaluation for each category.

Example:

Employee Table:

EmpID  Name
454    John

Evals Table:

id EmpID  Category  EvalDate     Note
1  454       A      2016-01-01   Excellent
2  454       A      2017-02-15   Not so good
3  454       B      2016-01-01   Poor
4  454       B      2017-02-01   Good

I need a query that returns the latest eval (the full row) for each category:

EmpID  Category  EvalDate     Note
454       A      2017-02-15   Not so good
454       B      2017-02-01   Good

My query so far:

SELECT 
    evals.EmpID,
    evals.Category,
    evals.Note,
    MAX(evals.EvalDate) as LatestEval
FROM
    evals
WHERE 
    evals.EmpID = 454
GROUP BY
    evals.Category

This unfortunately returns the latest date for each category but the note field from older evals.

Sergiu Z
  • 153
  • 1
  • 3
  • 12
  • 1
    Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – Uueerdo Nov 17 '17 at 22:52
  • @Uueerdo, not sure that's quite the same, I need grouping on multiple columns, empID and category. – Sergiu Z Nov 17 '17 at 22:57
  • 1
    That answer should still work; it just becomes a matter of joining on all grouped fields. – Uueerdo Nov 17 '17 at 23:16
  • @Uueerdo, you are correct. I figured it out, based on that question,I added a field in the group by in the sub-select AND a match on the inner join. The results are wicked fast too. Sloan's answer works too but I get 5 seconds on that query (11500 rows) and I get 0.4 seconds on this one. Thank you! – Sergiu Z Nov 18 '17 at 00:25
  • 1
    Sloane's answer is probably slower because the subquery is *correlated*; this often ends up in the subquery being executed repeatedly (for each external result). joining to an uncorrelated query (that groups all) ends up with a subquery that can take slightly longer to executed BUT is only executed once. _(On ridiculously large tables, correlated versions can be faster, especially if the outer query results in relatively few executions of the subquery.)_ – Uueerdo Nov 18 '17 at 00:38

1 Answers1

2

You might give this a try:

SELECT
    evals.EmpID,
    evals.Category,
    evals.Note,
    evals.EvalDate
FROM Employee emp
JOIN Evals evals
ON emp.EmpID = evals.EmpID
AND eval.EvalDate = 
    (SELECT MAX(EvalDate) 
    FROM Evals a 
    WHERE a.Category = evals.category 
        AND a.EmpID = emp.EmpID GROUP BY e.EmpID,e.Category)
GROUP BY evals.EmpID,evals.Category
Sloan Thrasher
  • 4,953
  • 3
  • 22
  • 40