We can achieve this using window partition functions. In MySQL, we can write the following query: SELECT *
FROM (
SELECT
*,
RANK() OVER (PARTITION BY Subject, ORDER BY Score DESC) rn
FROM Student
) t
WHERE rn = 1;