In ms sql I have stumble upon a question I have to do but can't wrap my mind around it.

I have simplified it for an worker situation will be able to affect my situation.

Employee: [EmpID, Name]
Case    : [CaseID, EmployeeID, CaseName, Profit]

How do you choose an employees most lucrative situation against his title?

I would like an effect 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

Observe that this isn't complete for those who have cases for workers with identical profits. For individuals cases, you'd add another join towards the mix