SQL Server 分組取每組最大值：GROUP BY 課程取最高分記錄

需求

表：id, course, student_id, score。每門課程取最高分記錄（含學生 ID）。

標準寫法 (ROW_NUMBER)

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY course ORDER BY score DESC, id ASC) AS rn
    FROM scores
)
SELECT id, course, student_id, score
FROM ranked
WHERE rn = 1;

簡化版 (僅需聚合值)

若僅需 MAX(score) 不需關聯學生 ID：

SELECT course, MAX(score) AS max_score
FROM scores
GROUP BY course;

舊版相容 (子查詢)

SELECT s1.*
FROM scores s1
JOIN (
    SELECT course, MAX(score) AS max_score
    FROM scores
    GROUP BY course
) s2 ON s1.course = s2.course AND s1.score = s2.max_score;

注意：最高分並列時會返回多條。