MySQL ORDER BY 與 GROUP BY 執行順序解析：分組後排序無效的陷阱與正確寫法

標準執行順序

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

GROUP BY 先於 ORDER BY 執行。

常見誤區

-- 錯誤預期：想取每組最新記錄
SELECT * FROM article_detail
WHERE userid = 1
GROUP BY userid
ORDER BY update_time DESC;

實際行為：先按 userid 分組（每組僅保留一行，**哪一行是不確定的**），再對這幾行做 ORDER BY。**無法保證取到最新行**。

正確寫法

聚合函數取極值

SELECT
    userid,
    SUM(click_num) AS total_click,
    MAX(update_time) AS latest_update,
    COUNT(*) AS total_articles
FROM article_detail
WHERE userid = 1
GROUP BY userid
ORDER BY latest_update DESC;

窗口函數（MySQL 8.0+）

SELECT * FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY userid ORDER BY update_time DESC) AS rn
    FROM article_detail
    WHERE userid = 1
) t WHERE rn = 1;

自連接取最大主鍵

SELECT a.*
FROM article_detail a
JOIN (
    SELECT userid, MAX(id) AS max_id
    FROM article_detail
    WHERE userid = 1
    GROUP BY userid
) b ON a.id = b.max_id;

核心原則

• GROUP BY 產生**分組聚合行**，非聚合列取值不確定（除非 ONLY_FULL_GROUP_BY 模式報錯）。
• ORDER BY 僅對最終結果集排序，**不影響分組內部行序**。
• 需「分組內排序取 Top-N」時，必須用窗口函數、子查詢或聚合函數。