SQL查询需求：如何仅返回OpportunityStageID最大值对应的完整行

以下是几种能实现需求的可行方案：

方法1：使用窗口函数ROW_NUMBER() #

适合需要处理复杂排序逻辑的场景，精准定位最大ID对应的行：

SELECT CPEID, OpportunityStageID, OpportunityStageName, CreateTS
FROM (
    SELECT 
        COH.CPEID,
        os.OpportunityStageID,
        OS.OpportunityStageName,
        COH.CreateTS,
        -- 按OpportunityStageID降序给每行编号
        ROW_NUMBER() OVER (ORDER BY os.OpportunityStageID DESC) AS rn
    FROM dbo.tblOpportunityStage AS OS 
    LEFT OUTER JOIN dbo.tblCPEOpportunityHistory AS COH 
        ON OS.OpportunityStageID = COH.OpportunityStageID
    WHERE COH.CPEID = 8437372
) AS sub_query
-- 取编号为1的行（即ID最大的行）
WHERE rn = 1;

方法2：子查询获取最大ID后关联查询 #

先定位目标CPEID对应的最大OpportunityStageID，再关联原表提取完整数据：

SELECT 
    COH.CPEID,
    os.OpportunityStageID,
    OS.OpportunityStageName,
    COH.CreateTS
FROM dbo.tblOpportunityStage AS OS 
JOIN dbo.tblCPEOpportunityHistory AS COH 
    ON OS.OpportunityStageID = COH.OpportunityStageID
WHERE COH.CPEID = 8437372
AND OS.OpportunityStageID = (
    -- 先查询当前CPEID下的最大OpportunityStageID
    SELECT MAX(OpportunityStageID)
    FROM dbo.tblCPEOpportunityHistory
    WHERE CPEID = 8437372
);

方法3：TOP 1结合ORDER BY #

如果确定最大OpportunityStageID唯一，直接取排序后的第一行即可：

SELECT TOP 1
    COH.CPEID,
    os.OpportunityStageID,
    OS.OpportunityStageName,
    COH.CreateTS
FROM dbo.tblOpportunityStage AS OS 
LEFT OUTER JOIN dbo.tblCPEOpportunityHistory AS COH 
    ON OS.OpportunityStageID = COH.OpportunityStageID
WHERE COH.CPEID = 8437372
-- 按OpportunityStageID降序排列，第一行就是最大ID的行
ORDER BY os.OpportunityStageID DESC;

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