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
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;