--===== WITHOUT TiesWITHcteEnumerateScores AS( SELECT StudentName, SubjectName, Score, ScoreOrder = RANK() OVER (PARTITION BY StudentName ORDER BY Score DESC) FROM dbo.YourTable) SELECT StudentName, SubjectName, Score FROM cteEnummerateScores WHERE ScoreOrder = 1 ORDER BY StudentName;

PARTITION BY StudentName

PARTITION BY SubjectName

select name, case when maths > science and maths > comp then 'maths'when comp > science and comp > maths then 'comp'else 'science'endfrom student

SELECT Name, x.* FROM StudentCROSS APPLY (SELECT TOP 1 * FROM (VALUES (maths,'maths'),(science,'science'),(com,'com')) d (Result,subject) ORDER BY Result DESC) x