I'm trying to get my intuitive approach correct (prior to having looked at Solution)... can someone help me debug?
SET @rank = 0; SELECT ranked.Id, ranked.Company, ranked.Salary FROM ( SELECT CASE WHEN @rank >= ( SELECT COUNT(*) FROM Employee e2 WHERE e2.Company = e1.Company ) THEN @rank:=1 ELSE @rank:=@rank+1 END AS col_index, Id, Company, Salary FROM Employee e1 ORDER BY Company, Salary, Id ) ranked LEFT JOIN (SELECT Company, FLOOR((COUNT(*)+1)/2) AS left_index, FLOOR((COUNT(*)+2)/2) AS right_index FROM Employee GROUP BY Company) indexes ON indexes.Company = ranked.Company WHERE indexes.left_index = ranked.col_index OR indexes.right_index = ranked.col_index
As a side note, I think the problem could be better specified.
- When a list is even, the median should be the average of the two middle numbers.
- What if there are more than two employees in a company with the median salary value? They way this problem is framed, we could not reliably come up with the same Id numbers (unless we sort by Id in the ranking, but this is an uncorrelated field).