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
How it operates in mysql