[SQL] 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:



What I actually got was this:



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

Continue reading...