多结果需筛选最新单价：SQL查询语句优化技术问询

Hey FrankyT, I see you're trying to pull the latest unit cost for specific items but your current query is returning multiple records instead of just the most recent one. Let's fix that up!

问题分析 #

Your existing query uses MAX("POPORH1"."DATE") but without proper grouping or filtering to tie that max date back to the corresponding unit cost for each item. That's why you're still getting multiple rows—we need to ensure we only grab the record where the PO date is the newest for each individual item.

解决方案1：使用窗口函数（推荐） #

Window functions like ROW_NUMBER() are perfect for this scenario. We'll partition the data by item number, sort each group by PO date in descending order, and then pick only the first row (which will be the latest entry) for each item.

Here's how to adjust your query:

WITH RankedItems AS (
    SELECT 
        "POPORH1"."DATE" AS "PO DATE",
        "ICSHEH"."DOCNUM",
        "ICSHEH"."TRANSDATE",
        "ICSHEH"."FISCYEAR",
        "ICSHEH"."FISCPERIOD",
        "ICSHEH"."REFERENCE",
        "ICSHED"."ITEMNO",
        "ICSHED"."ITEMDESC",
        "ICSHED"."LOCATION",
        "ICSHED"."QUANTITY",
        "ICSHED"."UNIT",
        "POPORL"."UNITCOST",
        -- Assign a rank to each item's records, newest first
        ROW_NUMBER() OVER (
            PARTITION BY "ICSHED"."ITEMNO" 
            ORDER BY "POPORH1"."DATE" DESC
        ) AS RowRank
    FROM 
        "CABDAT"."dbo"."ICSHEH" "ICSHEH"
        INNER JOIN "CABDAT"."dbo"."ICSHED" "ICSHED" 
            ON "ICSHEH"."SEQUENCENO" = "ICSHED"."SEQUENCENO"
        INNER JOIN "CABDAT"."dbo"."POPORL" "POPORL" 
            ON -- Add your join condition between ICSHED/POPORL here
        INNER JOIN "CABDAT"."dbo"."POPORH1" "POPORH1" 
            ON -- Add your join condition between POPORL/POPORH1 here
)
SELECT *
FROM RankedItems
WHERE RowRank = 1; -- Only keep the latest record per item

解决方案2：使用子查询筛选最新日期 #

If window functions aren't an option (e.g., older SQL server versions), you can first get the latest PO date for each item, then join back to your main tables to get the corresponding cost:

SELECT 
    LatestPOs."PO DATE",
    "ICSHEH"."DOCNUM",
    "ICSHEH"."TRANSDATE",
    "ICSHEH"."FISCYEAR",
    "ICSHEH"."FISCPERIOD",
    "ICSHEH"."REFERENCE",
    "ICSHED"."ITEMNO",
    "ICSHED"."ITEMDESC",
    "ICSHED"."LOCATION",
    "ICSHED"."QUANTITY",
    "ICSHED"."UNIT",
    "POPORL"."UNITCOST"
FROM 
    "CABDAT"."dbo"."ICSHEH" "ICSHEH"
    INNER JOIN "CABDAT"."dbo"."ICSHED" "ICSHED" 
        ON "ICSHEH"."SEQUENCENO" = "ICSHED"."SEQUENCENO"
    INNER JOIN "CABDAT"."dbo"."POPORL" "POPORL" 
        ON -- Add join condition here
    INNER JOIN "CABDAT"."dbo"."POPORH1" "POPORH1" 
        ON -- Add join condition here
    INNER JOIN (
        -- Subquery to get latest PO date per item
        SELECT 
            "ICSHED"."ITEMNO",
            MAX("POPORH1"."DATE") AS "PO DATE"
        FROM 
            "CABDAT"."dbo"."ICSHED" "ICSHED"
            INNER JOIN "CABDAT"."dbo"."POPORL" "POPORL" 
                ON -- Match join conditions from main query
            INNER JOIN "CABDAT"."dbo"."POPORH1" "POPORH1" 
                ON -- Match join conditions from main query
        GROUP BY "ICSHED"."ITEMNO"
    ) AS LatestPOs
        ON "ICSHED"."ITEMNO" = LatestPOs."ITEMNO"
        AND "POPORH1"."DATE" = LatestPOs."PO DATE";

关键注意事项 #

• Make sure to fill in the missing join conditions between your tables (I left comments where they're needed) so the query can properly relate purchase order lines to the header dates.
• If multiple records exist for the same item on the latest PO date, the window function approach will pick one arbitrarily. If you need to handle ties (e.g., pick the highest cost or most recent transdate), adjust the ORDER BY in the window function to include those additional columns.

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