SQL如何查询每个SpellID下Episode_Order最高值对应的EpisodeID

你现有SQL无法得到预期结果的核心原因是将Episode_ID加入了GROUP BY分组字段：由于Episode_ID是全局唯一值，分组后每个分组仅对应1条原始记录，计算出的MAX(Episode_Order)实际就是当前行本身的Episode_Order值，无法实现按Spell_ID分组取最大排序对应行的需求。

推荐使用窗口函数实现，兼容绝大多数主流SQL dialect（SQL Server、PostgreSQL、MySQL 8.0+等都支持）：

SELECT TOP(100) Spell_ID, Episode_ID, Episode_Order AS M
FROM (
    SELECT 
        Spell_ID, 
        Episode_ID, 
        Episode_Order,
        -- 按Spell_ID分组，Episode_Order倒序排列，排名第一的就是分组内最大值
        ROW_NUMBER() OVER (PARTITION BY Spell_ID ORDER BY Episode_Order DESC) AS rn
    FROM [dbo].[Client_MidEssex_Inpatient_Episodes_Landing]
) t
WHERE rn = 1

如果你使用的是不支持窗口函数的低版本数据库，也可以用关联子查询的写法：

SELECT TOP(100) a.Spell_ID, a.Episode_ID, a.Episode_Order AS M
FROM [dbo].[Client_MidEssex_Inpatient_Episodes_Landing] a
INNER JOIN (
    SELECT Spell_ID, MAX(Episode_Order) AS max_order
    FROM [dbo].[Client_MidEssex_Inpatient_Episodes_Landing]
    GROUP BY Spell_ID
) b ON a.Spell_ID = b.Spell_ID AND a.Episode_Order = b.max_order

内容的提问来源于stack exchange，提问作者user15464793