如何为每个唯一ID选择一行

我的名字

在这里学习SQL的新手在职，还是一个新手。我有一个我不知道该如何克服的问题。使用IBM Netezza和Aginity Workbench。

我当前的输出将根据创建任务的时间尝试为每个案例号返回一行。它将仅使该行具有最新任务。这使我大约达到了其中的85％。问题在于，有时多个任务在同一天具有创建日期。

我想合并“任务跟踪日期”，以仅在有多个具有相同“案例号”的行时保留最新行。我张贴了一个示例，说明我当前的代码输出以及我希望输出的代码。

当前代码

SELECT 
     A.PS_CASE_ID AS Case_Number
     ,D.CASE_TASK_TYPE_NM AS Task
     ,C.TASK_CRTE_TMS
     ,C.TASK_FLWUP_DT AS Task_Followup_Date


FROM VW_CC_CASE A
     INNER JOIN VW_CASE_TASK C ON (A.CASE_ID = C.CASE_ID)
     INNER JOIN VW_CASE_TASK_TYPE D ON (C.CASE_TASK_TYPE_ID = D.CASE_TASK_TYPE_ID)
     INNER JOIN ADMIN.VW_RSN_CTGY B ON (A.RSN_CTGY_ID = B.RSN_CTGY_ID)


WHERE 
     (A.PS_Z_SPSR_ID LIKE '%EFT' OR A.PS_Z_SPSR_ID LIKE '%CRDT')
     AND CAST(A.CASE_CRTE_TMS AS DATE) >= '2020-01-01'
     AND B.RSN_CTGY_NM = 'Chargeback Initiation'
     AND CAST(C.TASK_CRTE_TMS AS DATE) = (SELECT MAX(CAST(C2.TASK_CRTE_TMS AS DATE)) from VW_CASE_TASK C2 WHERE C2.CASE_ID = C.CASE_ID)


GROUP BY 
     A.PS_CASE_ID
     ,D.CASE_TASK_TYPE_NM
     ,C.TASK_CRTE_TMS
     ,C.TASK_FLWUP_DT

电流输出

所需的输出

蒂姆·比格莱森（Tim Biegeleisen）

您可以ROW_NUMBER在这里使用：

WITH cte AS (
    SELECT DISTINCT A.PS_CASE_ID AS Case_Number, D.CASE_TASK_TYPE_NM AS Task,
        C.TASK_CRTE_TMS, C.TASK_FLWUP_DT AS Task_Followup_Date,
        ROW_NUMBER() OVER (PARTITION BY A.PS_CASE_ID ORDER BY C.TASK_FLWUP_DT DESC) rn
    FROM VW_CC_CASE A
    INNER JOIN VW_CASE_TASK C ON A.CASE_ID = C.CASE_ID
    INNER JOIN VW_CASE_TASK_TYPE D ON C.CASE_TASK_TYPE_ID = D.CASE_TASK_TYPE_ID
    INNER JOIN ADMIN.VW_RSN_CTGY B ON A.RSN_CTGY_ID = B.RSN_CTGY_ID
    WHERE (A.PS_Z_SPSR_ID LIKE '%EFT' OR A.PS_Z_SPSR_ID LIKE '%CRDT') AND
          CAST(A.CASE_CRTE_TMS AS DATE) >= '2020-01-01' AND
          B.RSN_CTGY_NM = 'Chargeback Initiation' AND
          CAST(C.TASK_CRTE_TMS AS DATE) = (SELECT MAX(CAST(C2.TASK_CRTE_TMS AS DATE))
                                           FROM VW_CASE_TASK C2
                                           WHERE C2.CASE_ID = C.CASE_ID)
)

SELECT
    Case_Number,
    Task,
    TASK_CRTE_TMS,
    Task_Followup_Date
FROM cte
WHERE rn = 1;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。