如何在ROW_NUMBER后使用MAX函数获取最新采购订单数据

Got it, let's figure out how to get the latest purchase order record for each PO_Number using the MAX() function, ditching the WHERE Line_Count = 1 clause from your original query. Here are two reliable approaches:

Approach 1: Use Window Functions with MAX() #

We can calculate the latest date per PO_Number directly in the subquery, then filter records that match this date:

SELECT *
FROM (
    SELECT 
        *,
        MAX(Date_PurchaseOrder) OVER (PARTITION BY PO_Number) AS Latest_Purchase_Date
    FROM Table_PO
) AS T
WHERE Date_PurchaseOrder = Latest_Purchase_Date

How this works: #

• The MAX(Date_PurchaseOrder) OVER (PARTITION BY PO_Number) window function computes the most recent date for each unique PO_Number.
• We then filter the outer query to only keep rows where the record's Date_PurchaseOrder equals this latest date, giving us the newest entry for each PO.

Approach 2: Aggregate Subquery with JOIN #

If you prefer a more traditional aggregate approach, you can first get the latest date per group, then join back to the original table:

SELECT t.*
FROM Table_PO t
INNER JOIN (
    -- Get the latest date for each PO_Number + PO_Value + Supplier group
    SELECT 
        PO_Number, 
        PO_Value, 
        Supplier, 
        MAX(Date_PurchaseOrder) AS Latest_Purchase_Date
    FROM Table_PO
    GROUP BY PO_Number, PO_Value, Supplier
) AS agg 
    ON t.PO_Number = agg.PO_Number 
    AND t.PO_Value = agg.PO_Value 
    AND t.Supplier = agg.Supplier 
    AND t.Date_PurchaseOrder = agg.Latest_Purchase_Date

How this works: #

• The subquery groups by your original partition columns (PO_Number, PO_Value, Supplier) and grabs the maximum (newest) Date_PurchaseOrder for each group.
• We join this aggregated result back to the original table using all grouping columns plus the latest date, which pulls in only the records that match the newest date for each group.

Note on duplicate latest dates: #

If multiple records share the same latest Date_PurchaseOrder for a single PO_Number group, both approaches will return all those records. If you only want one record per group (like your original ROW_NUMBER() approach did), you can combine the window function method with ROW_NUMBER() to break ties:

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY PO_Number 
            ORDER BY Date_PurchaseOrder DESC, [AnyOtherColumnToBreakTies] DESC
        ) AS Line_Count
    FROM Table_PO
) AS T
WHERE Line_Count = 1

This still uses a WHERE clause, but it's worth mentioning if you need to handle edge cases with duplicate latest dates.

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