SQL订单分组查询优化：按InsertionTimestamp排序且无重复

原查询的分组逻辑是正确的，但直接对InsertionTimestamp排序会报错（该字段不在聚合或分组列中）；而将其加入GROUP BY会拆分原有分组，导致重复订单。以下是正确的实现方式：

核心思路 #

为每个分组（Purchase_Order_Number+Account_Number+Product_Type）确定一个代表的插入时间（比如分组内最早/最晚的InsertionTimestamp），以此作为排序依据，同时保留原分组的聚合逻辑。

实现代码 #

方案一：子查询方式 #

SELECT 
    Purchase_Order_Number, 
    Account_Number, 
    LineNumber, 
    Product_Type
FROM (
    SELECT 
        Purchase_Order_Number, 
        Account_Number, 
        MAX(LineNumber) AS LineNumber, 
        Product_Type,
        MIN(InsertionTimestamp) AS GroupInsertionTime -- 取分组内最早的插入时间作为排序依据
    FROM dbo.PDF_Orders_Validation 
    WHERE BotReady = 'Y' -- 注意：原查询条件为BotReady='Y'，请确认业务逻辑是否需要修改
    GROUP BY Purchase_Order_Number, Account_Number, Product_Type
) AS GroupedOrders
ORDER BY GroupInsertionTime ASC;

方案二：CTE（公共表表达式）方式 #

WITH GroupedOrders AS (
    SELECT 
        Purchase_Order_Number, 
        Account_Number, 
        MAX(LineNumber) AS LineNumber, 
        Product_Type,
        MIN(InsertionTimestamp) AS GroupInsertionTime
    FROM dbo.PDF_Orders_Validation 
    WHERE BotReady = 'Y'
    GROUP BY Purchase_Order_Number, Account_Number, Product_Type
)
SELECT 
    Purchase_Order_Number, 
    Account_Number, 
    LineNumber, 
    Product_Type
FROM GroupedOrders
ORDER BY GroupInsertionTime ASC;

说明 #

1. 如果需要按分组内最新的插入时间排序，只需将MIN(InsertionTimestamp)替换为MAX(InsertionTimestamp)即可。
2. 注意你尝试的方案中误将BotReady = 'Y'改为BotReady = ''，请根据实际业务需求确认过滤条件。
3. 该方案严格保留原分组逻辑，不会生成重复订单，同时满足按插入时间排序的要求。

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