SQL技术问询：如何正确查询各分组的max(po_date)及对应min(cost)

First, let's break down why your current query isn't giving the right results:

• Your subquery select max(po_date) from minmaxtest returns the global latest PO date (which is '09/30/15' in your dataset), not the latest date for each individual product group.
• Similarly, select min(cost) from minmaxtest grabs the global minimum cost (8), but that value comes from an earlier date for some product groups.
• Combining these two global values gives you rows where the PO date is the overall latest AND cost is the overall lowest—this doesn't apply the logic per product group, which is what you actually need.

Here are two reliable solutions tailored to your requirement:

Solution 1: Using Window Functions (Modern SQL, Most Efficient) #

This approach uses ROW_NUMBER() to rank rows within each product group, prioritizing the latest PO date first, then the lowest cost on that date. We then select only the top-ranked row per group:

WITH ranked_product_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY mfg, mfgid, [Desc] 
            ORDER BY po_date DESC, cost ASC
        ) AS row_rank
    FROM minmaxtest
)
SELECT id, mfg, mfgid, [Desc], po_date, cost
FROM ranked_product_data
WHERE row_rank = 1;

How this works: #

• PARTITION BY mfg, mfgid, [Desc] splits the dataset into groups for each unique product (based on manufacturer, manufacturer ID, and description).
• ORDER BY po_date DESC ensures we first look at the most recent PO date in each group.
• cost ASC guarantees that if multiple rows exist on the latest date, we pick the one with the smallest cost.
• row_rank = 1 filters to only keep the top-ranked row per product group.

Solution 2: Using Subqueries (Compatible with Older SQL Versions) #

If you're working with a SQL system that doesn't support CTEs or window functions, nested subqueries will get the job done:

SELECT m.*
FROM minmaxtest m
-- Join to get the max PO date per product group
INNER JOIN (
    SELECT mfg, mfgid, [Desc], MAX(po_date) AS latest_po_date
    FROM minmaxtest
    GROUP BY mfg, mfgid, [Desc]
) group_max_dates 
    ON m.mfg = group_max_dates.mfg 
    AND m.mfgid = group_max_dates.mfgid 
    AND m.[Desc] = group_max_dates.[Desc] 
    AND m.po_date = group_max_dates.latest_po_date
-- Join again to get the min cost on that max PO date per group
INNER JOIN (
    SELECT mfg, mfgid, [Desc], po_date, MIN(cost) AS lowest_cost_on_date
    FROM minmaxtest
    GROUP BY mfg, mfgid, [Desc], po_date
) group_min_costs 
    ON m.mfg = group_min_costs.mfg 
    AND m.mfgid = group_min_costs.mfgid 
    AND m.[Desc] = group_min_costs.[Desc] 
    AND m.po_date = group_min_costs.po_date 
    AND m.cost = group_min_costs.lowest_cost_on_date;

Expected Result for Your Test Data #

Both queries will return these rows, which match your requirement perfectly:

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