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