A complex mysql “over” query SELECT Candidates.CID, Candidates.NAME, Candidate_Scores.SCORE FROM Candidates, (SELECT CID, SCORE FROM ( SELECT CID, SCORE, Rank() over (Partition BY CID ORDER BY SCORE DESC ) AS Rank FROM Candidate_Scores ) rs WHERE Rank