在SQL中查找具有最大值的记录

WITH ClassScores AS(    SELECt 1 AS class, 'Anna' AS name, 34 AS score     UNIOn    SELECT 1, 'Andy', 80      UNIOn    SELECT 2, 'Brooke', 90      UNIOn    SELECT 2, 'Brad', 70      UNIOn    SELECT 3, 'Charles', 67      UNIOn    SELECT 3, 'Christina', 66 )SELECT C1.Class, C1.Name, C1.Score  FROM ClassScores AS C1  JOIN (SELECt Class, MAX(Score) AS MaxScore          FROM ClassScores         GROUP BY Class       ) AS C2    ON C1.Class = C2.Class   AND C1.Score = C2.MaxScore ORDER BY C1.Class;