How do I select employees most profitable case's?

In ms sql I've run across a query I need to do but can't wrap my head around it.

I've simplified it to an employee case that I can apply to my situation.

Employee: [E开发者_C百科mpID, Name]
Case    : [CaseID, EmployeeID, CaseName, Profit]

How do I select an employees most profitable case against his name?

I'd like a result like,

Result  : [EmpID, Name, CaseID, CaseName, Profit]

---

SELECT *
FROM   Employee e
       INNER JOIN Case c ON c.EmployeeID = e.EmpID
       INNER JOIN (
         SELECT EmployeeID, MAX(Profit) AS Profit
         FROM   Case
         GROUP BY 
                EmployeeID
       ) pmax ON pmax.EmployeeID = c.EmployeeID
                 AND pmax.Profit = c.Profit

Note that this is not complete if you have cases for employees with identical profits. For those cases, you would add another join to the mix

---

How it works in mysql

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html