sql - How do I select employees most profitable case's? -

in ms sql i've run across query need can't wrap head around it.

i've simplified employee case can apply situation.

employee: [empid, name] case    : [caseid, employeeid, casename, profit] 

how select employees profitable case against name?

i'd result like,

result  : [empid, name, caseid, casename, profit] 

select *   employee e        inner join case c on c.employeeid = e.empid        inner join (          select employeeid, max(profit) profit            case          group                  employeeid        ) pmax on pmax.employeeid = c.employeeid                  , pmax.profit = c.profit 

note not complete if have cases employees identical profits. cases, add join mix