如何修改SQL查询以仅返回最大创建日期的记录？

Got it, let's tweak your query to return only the latest record for each lot based on STOQLYH.CREDAT_0. The simplest way to do this is using a window function like ROW_NUMBER() to rank records per lot by creation date, then filter for the top-ranked entry.

Here's the modified query:

SELECT 
    variety,
    crop_year,
    lot_number,
    product_name,
    uv_alpha,
    uv_beta,
    hsi,
    moisture,
    oil_by_dist,
    hplc_alpha,
    hplc_beta,
    hplc_cohumulone,
    hplc_colupulone,
    mebak_alpha_7_5,
    lcv_alpha_7_4,
    oil_b_pinene,
    oil_myrcene,
    oil_linalool,
    oil_caryophyllene,
    oil_farnesene,
    oil_humulene,
    oil_geraniol,
    created_at
FROM (
    SELECT 
        ZITMTSICOD6.LNGDES_0 as variety,
        STOLOT.YCROPYR_0 as crop_year,
        STOLOT.LOT_0 as lot_number,
        ZITMTSICOD2.LNGDES_0 AS product_name,
        ISNULL([UV_Alpha], 'NA') as uv_alpha,
        ISNULL([UV_Beta], 'NA') as uv_beta,
        ISNULL([HSI], 'NA') as hsi,
        ISNULL(Moisture, 'NA') as moisture,
        ISNULL(Oil_by_Dist,'NA') as oil_by_dist,
        ISNULL([HPLC Alpha],'NA') as hplc_alpha,
        ISNULL([HPLC Beta],'NA') as hplc_beta,
        ISNULL([HPLC Cohumulone],'NA') as hplc_cohumulone,
        ISNULL([HPLC Colupulone],'NA') as hplc_colupulone,
        ISNULL([Mebak Alpha 7.5],'NA') as mebak_alpha_7_5,
        ISNULL([LCV Alpha 7.4],'NA') as lcv_alpha_7_4,
        ISNULL(Oil_B_Pinene,'NA') as oil_b_pinene,
        ISNULL(Oil_Myrcene,'NA') as oil_myrcene,
        ISNULL(Oil_Linalool,'NA') as oil_linalool,
        ISNULL(Oil_Caryophyllene,'NA') as oil_caryophyllene,
        ISNULL(Oil_Farnesene,'NA') as oil_farnesene,
        ISNULL(Oil_Humulene,'NA') as oil_humulene,
        ISNULL(Oil_Geraniol,'NA') as oil_geraniol,
        STOQLYH.CREDAT_0 as created_at,
        -- Rank records per lot, newest first
        ROW_NUMBER() OVER (
            PARTITION BY STOLOT.LOT_0 
            ORDER BY STOQLYH.CREDAT_0 DESC
        ) AS record_rank
    FROM LIVE.STOLOT 
    left outer join LIVE.STOQLYD on STOLOT.LOT_0 = STOQLYD.LOT_0 and STOLOT.SLO_0 = STOQLYD.SLO_0 and STOQLYD.ITMREF_0 = STOLOT.ITMREF_0 
    left outer join LIVE.STOQLYH on STOQLYH.VCRNUM_0 = STOQLYD.VCRNUM_0 and STOQLYH.ITMREF_0 = STOQLYD.ITMREF_0 
    left outer join LIVE.ITMMASTER on STOLOT.ITMREF_0 = ITMMASTER.ITMREF_0 
    left outer join LIVE.ZITMTSICOD6 on ITMMASTER.TSICOD_6 = ZITMTSICOD6.ID_0 
    left outer join LIVE.ZITMTSICOD2 on ITMMASTER.TSICOD_2 = ZITMTSICOD2.ID_0 
    left outer join ( 
        SELECT QLYCTLDEM_0, QLYCRDASW.VCRLIN_0, ITMREF_0, 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'UVALP110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'UV_Alpha', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'UVBET110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'UV_Beta', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'UVHSI110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'HSI', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'OVMOI110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'Moisture', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'DIOIL110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'Oil_by_Dist', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'HPALP110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'HPLC Alpha', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'HPBET110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'HPLC Beta', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'HPCOH110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'HPLC Cohumulone', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'HPCOL110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'HPLC Colupulone', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'LCALP110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'Mebak Alpha 7.5', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'LCALP310' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'LCV Alpha 7.4', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'GCBPI110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'Oil_B_Pinene', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'GCMYR110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'Oil_Myrcene', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'GCLIN110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'Oil_Linalool', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'GCCAR110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'Oil_Caryophyllene', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'GCFAR110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'Oil_Farnesene', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'GCHUM110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'Oil_Humulene', 
        MAX(CASE WHEN QLYCRDASW.QSTNUM_0 = 'GCGEL110' THEN QLYCRDASW.ASW_0 ELSE NULL END) AS 'Oil_Geraniol' 
        FROM LIVE.QLYCRDASW 
        GROUP BY QLYCTLDEM_0, QLYCRDASW.VCRLIN_0, ITMREF_0 
    ) AS QLYCRDASW ON (STOQLYD.VCRNUM_0 = QLYCRDASW.QLYCTLDEM_0) AND (STOQLYD.VCRLIN_0 = QLYCRDASW.VCRLIN_0) AND (STOQLYD.ITMREF_0 = QLYCRDASW.ITMREF_0) 
    WHERE STOLOT.LOT_0 In( 
        SELECT Item 
        FROM dbo.SplitString('PL1-YKUCTZ0012',',')
    ) 
    AND (
        ISNUMERIC(UV_Alpha) = 1 
        OR ISNUMERIC(UV_Beta) = 1 
        OR ISNUMERIC(HSI) = 1 
        OR ISNUMERIC(Moisture) = 1 
        AND ISNUMERIC(Oil_by_Dist) = 1 
        OR ISNUMERIC([HPLC Alpha]) = 1 
        OR ISNUMERIC([HPLC Beta]) = 1 
        OR ISNUMERIC([HPLC Cohumulone]) = 1 
        OR ISNUMERIC([HPLC Colupulone]) = 1 
        OR ISNUMERIC([Mebak Alpha 7.5]) = 1 
        OR ISNUMERIC([LCV Alpha 7.4]) = 1 
        OR ISNUMERIC(Oil_B_Pinene) = 1 
        OR ISNUMERIC(Oil_Myrcene) = 1 
        OR ISNUMERIC(Oil_Linalool) = 1 
        OR ISNUMERIC(Oil_Caryophyllene) = 1 
        OR ISNUMERIC(Oil_Farnesene) = 1 
        OR ISNUMERIC(Oil_Humulene) = 1 
        OR ISNUMERIC(Oil_Geraniol) = 1
    )
) AS ranked_records
WHERE record_rank = 1;

Key Changes Explained: #

• Added a subquery wrapper around your original query, and included ROW_NUMBER() OVER (PARTITION BY STOLOT.LOT_0 ORDER BY STOQLYH.CREDAT_0 DESC) AS record_rank:
  • PARTITION BY STOLOT.LOT_0 groups records by each unique lot number.
  • ORDER BY STOQLYH.CREDAT_0 DESC sorts each group from newest to oldest creation date.
  • This assigns a rank of 1 to the most recent record for each lot.
• The outer query filters for record_rank = 1, so only the latest entry per lot is returned.
• Removed the DISTINCT keyword since the row numbering ensures one unique record per lot.

Quick Note: #

If there's a chance multiple records have the exact same maximum creation date for a lot and you want to keep all of them, replace ROW_NUMBER() with RANK() instead. But since you asked for only one row, ROW_NUMBER() is the right fit.

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