ORDER BY子句报错：MAX聚合函数为何未被识别？

一、报错原因 #

Column INSTR.JOB_HDR.JOB_START_DTTM在ORDER BY子句中无效这条错误的核心逻辑是：

• GROUP BY分组后，每个分组包含多条原始的JOB_START_DTTM记录，数据库无法确定你要基于哪一条原始数据排序。
• 虽然SELECT里用了MAX(JOB_START_DTTM)做聚合，但ORDER BY里引用的是未聚合的原始列JOB_HDR.JOB_START_DTTM，它不在GROUP BY列表中，也没被聚合函数包裹，违反了SQL分组后的字段使用规则。

二、GROUP BY加字段导致多记录的原因 #

当前GROUP BY包含JOB_STATUS，这意味着同一个JOB_NAME如果对应不同的JOB_STATUS（比如同时存在COMPLETE和RUNNING状态的记录），会被拆分成不同分组，自然输出多条结果，和你“每个JOB_NAME仅需最新一条记录”的需求冲突。

你提到的“MAX失效”是误解：MAX函数本身正常工作，但分组逻辑错误导致无法得到单一的最新记录。

三、CASE语句处理JOB_STATUS的作用 #

导师提到的CASE语句，核心是定义状态优先级——比如同一个JOB_NAME既有RUNNING又有COMPLETE状态时，RUNNING是当前正在执行的，优先级高于历史完成的记录，需要优先保留。示例写法：

CASE 
    WHEN JOB_STATUS = 'RUNNING' THEN 1
    WHEN JOB_STATUS = 'COMPLETE' THEN 2
    ELSE 3
END AS STATUS_PRIORITY

将这个优先级字段加入排序规则，就能确保取到最新且优先级最高的状态记录。

四、正确实现需求的查询写法 #

要获取每个JOB_NAME的最新运行时间及状态，推荐用窗口函数ROW_NUMBER()，它能给每个JOB_NAME的记录按时间排序，标记出最新的那一条：

WITH LatestJobs AS (
    SELECT 
        JB.JOB_NAME,
        JB.JOB_DESCR,
        JB.INFA_FOLDER_NAME,
        JB.WORKFLOW_NAME,
        JB.LOAD_GROUP,
        JOB_HDR.JOB_START_DTTM AS 'JOB START',
        JOB_HDR.JOB_END_DTTM AS 'JOB END',
        JOB_HDR.JOB_STATUS,
        -- 按JOB_NAME分组，先按启动时间倒序，再按状态优先级倒序（RUNNING优先）
        ROW_NUMBER() OVER (
            PARTITION BY JB.JOB_NAME 
            ORDER BY 
                JOB_HDR.JOB_START_DTTM DESC,
                CASE WHEN JOB_HDR.JOB_STATUS = 'RUNNING' THEN 1 ELSE 2 END
        ) AS RN
    FROM [INSTR].[JOB] JB
    INNER JOIN [INSTR].[JOB_HDR] JOB_HDR
        ON JB.JOB_ID = JOB_HDR.JOB_ID
    WHERE 
        JB.JOB_NAME LIKE 'EDW%1D00%'
        AND JOB_HDR.JOB_STATUS IN ('COMPLETE', 'RUNNING')
)
SELECT 
    JOB_NAME,
    JOB_DESCR,
    INFA_FOLDER_NAME,
    WORKFLOW_NAME,
    LOAD_GROUP,
    'JOB START',
    'JOB END',
    JOB_STATUS
FROM LatestJobs
WHERE RN = 1; -- 仅保留每个JOB_NAME的第一条（最新）记录

备选：GROUP BY写法（不推荐，可读性差） #

如果必须用GROUP BY实现，需要用MAX结合CASE关联最新时间对应的状态：

SELECT 
    JB.JOB_NAME,
    JB.JOB_DESCR,
    JB.INFA_FOLDER_NAME,
    JB.WORKFLOW_NAME,
    JB.LOAD_GROUP,
    MAX(JOB_HDR.JOB_START_DTTM) AS 'JOB START',
    MAX(JOB_HDR.JOB_END_DTTM) AS 'JOB END',
    -- 提取最新启动时间对应的状态
    MAX(CASE 
        WHEN JOB_HDR.JOB_START_DTTM = MAX(JOB_HDR.JOB_START_DTTM) OVER (PARTITION BY JB.JOB_NAME)
        THEN JOB_HDR.JOB_STATUS 
    END) AS JOB_STATUS
FROM [INSTR].[JOB] JB
INNER JOIN [INSTR].[JOB_HDR] JOB_HDR
    ON JB.JOB_ID = JOB_HDR.JOB_ID
WHERE 
    JB.JOB_NAME LIKE 'EDW%1D00%'
    AND JOB_HDR.JOB_STATUS IN ('COMPLETE', 'RUNNING')
GROUP BY 
    JB.JOB_NAME,
    JB.JOB_DESCR,
    JB.INFA_FOLDER_NAME,
    JB.WORKFLOW_NAME,
    JB.LOAD_GROUP
ORDER BY 'JOB START' DESC, 'JOB END' DESC;

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