CODEWITHSUNDEEP
sqlnetezza
MyNameHere
MyNameHere

Reputation: 305

How to select a single row for each unique ID

SQL novice here learning on the job, still a greenhorn. I have a problem I don't know how to overcome. Using IBM Netezza and Aginity Workbench.

My current output will try to return one row per case number based on when a task was created. It will only keep the row with the newest task. This gets me about 85% of the way there. The issue is that sometimes multiple tasks have a create day of the same day.

I would like to incorporate Task Followup Date to only keep the newest row if there are multiple rows with the same Case Number. I posted an example of what my current code outputs and what i would like it to output.

Current code

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

Current output

Current output

Desired output

Desired output

Upvotes: 1

Views: 206

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

You could use ROW_NUMBER here:

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

One method used window functions:

with cte as (
      < your query here >
     )
select x.*
from (select cte.*,
             row_number() over (partition by case_number, Task_Followup_Date
                                order by TASK_CRTE_TMS asc
                               ) as seqnum
      from cte
     ) x
where seqnum = 1;

Upvotes: 0

Related Questions