sqloracle-databaseminrank

Max and Rank not returning expected results


I have what I thought was a simple solution to return the earliest date in each group for a large number of records.

WITH FEES
AS
(
SELECT
    IT_NO               AS ITEM_NO,
    MAX(FEE_TRAN_DT)    AS FIRST_FEE_ASSESSED_DATE,
    RUN_DATE,
    RNK
FROM
(
    SELECT
        IT_NO,
        FEE_TRAN_TY,
        FEE_TRAN_AM,
        FEE_TRAN_DT,
        RUN_DATE,

    RANK() OVER
        (PARTITION BY 
            IT_NO,
            FEE_TRAN_DT,
            RUN_DATE
        ORDER BY 
            IT_NO,
            FEE_TRAN_DT,
            RUN_DATE)
    AS RNK
    FROM DM_MORTGAGE.FEE 
    WHERE 
        FEE_TRAN_DT > '01-MAY-20' AND
        FEE_TRAN_TY = 'A' --AND 
)
GROUP BY IT_NO, FEE_TRAN_DT,  RUN_DATE ,RNK
ORDER BY IT_NO, FEE_TRAN_DT,  RUN_DATE, RNK
)
SELECT * FROM FEES;

I wanted to see is this:

enter image description here

What I actually got was this:

enter image description here

I've struggle with this for way too long, and am unable to figure out what is going wrong. Any help would be most welcome. Thanks


Solution

  • Don't include the dates in the PARTITION. Just PARTITION BY it_no and then ORDER BY the dates (and you don't need to use MAX - which would be wrong as you want the earliest not the latest, instead, filter on the rank):

    WITH FEES AS (
      SELECT IT_NO,
             FEE_TRAN_TY,
             FEE_TRAN_AM,
             FEE_TRAN_DT,
             RUN_DATE,
             RANK() OVER (
               PARTITION BY IT_NO
               ORDER BY FEE_TRAN_DT, RUN_DATE
             ) AS rnk
      FROM   DM_MORTGAGE.FEE 
      WHERE  FEE_TRAN_DT > DATE '2020-05-01'
      AND    FEE_TRAN_TY = 'A'
    )
    SELECT *
    FROM   FEES
    WHERE  rnk = 1;