How to get max of one date in a table in sql

Question

I have written the below code -

select 
        PAPF.PERSON_NUMBER, 
        BP.NAME                                       BENEFIT_PLAN, 
        BBR.BENEFIT_RELATION_NAME, 
        Round(BPER.BNFT_AMT, 2) * 100                 COVERAGE_AMOUNT, 
        TO_CHAR(BPER.ENRT_CVG_STRT_DT, 'YYYYMMDD')    ENROLMENTCOVSTARTDATE
        TO_CHAR(BPER.ENRT_CVG_THRU_DT, 'YYYYMMDD')    ENROLMENTCOVSENDDATE
FROM    PER_ALL_PEOPLE_F               PAPF,
        BEN_PRTT_ENRT_RSLT             BPER, 
        BEN_PL_F                       BP, 
        BEN_BENEFIT_RELATIONS_F        BBR
        WHERE PAPF.PERSON_ID               = BPER.PERSON_ID 
        AND     BPER.PL_ID                   = BP.PL_ID 
        AND     BBR.PERSON_ID                = PAPF.PERSON_ID 
AND     BBR.BENEFIT_RELATION_NAME    = 'DFLT'
AND     SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE 
AND     SYSDATE BETWEEN BP.EFFECTIVE_START_DATE   AND BP.EFFECTIVE_END_DATE 
AND     SYSDATE BETWEEN BBR.EFFECTIVE_START_DATE  AND BBR.EFFECTIVE_END_DATE 

This will give me an output like -

PErson_number           BENEFIT_PLAN                COVERAGE_AMOUNT  ENROLMENTCOVSTARTDATE          ENROLMENTCOVSENDDATE    
1010                    US Basic PLAN               20000               20200901                    20201020
1010                    US Basic PLAN               20000               20201021                    

1011                    Us Spouse PLAN              160000              20200901                    20201020
1011                    Us Spouse PLAN              160000              20201021                    47121231

I want to retrive only the max of the ENROLMENTCOVSTARTDATE for each person. The expected output should be -

PErson_number           BENEFIT_PLAN                COVERAGE_AMOUNT  ENROLMENTCOVSTARTDATE          ENROLMENTCOVSENDDATE    
1010                    US Basic PLAN               20000               20201021                    

1011                    Us Spouse PLAN              160000              20201021                    20201220

how can i use Max in the main query for this ?

Answer 1

First rank the rows by their ENRT_CVG_STRT_DT per person (assumed identified by PAPF.PERSON_NUMBER). Then filter on rows that are top.

select person_number,
       name,
       BENEFIT_PLAN,
       BENEFIT_RELATION_NAME,
       COVERAGE_AMOUNT,
       ENROLMENTCOVSTARTDATE,
       ENROLMENTCOVSENDDATE
from (
select 
        PAPF.PERSON_NUMBER, 
        BP.NAME                                       BENEFIT_PLAN, 
        BBR.BENEFIT_RELATION_NAME, 
        Round(BPER.BNFT_AMT, 2) * 100                 COVERAGE_AMOUNT, 
        TO_CHAR(BPER.ENRT_CVG_STRT_DT, 'YYYYMMDD')    ENROLMENTCOVSTARTDATE,
            TO_CHAR(BPER.ENRT_CVG_THRU_DT, 'YYYYMMDD')    ENROLMENTCOVSENDDATE,
        row_number() over (partition by PAPF.PERSON_NUMBER order by BPER.ENRT_CVG_STRT_DT desc) rn 
        from    
FROM    PER_ALL_PEOPLE_F               PAPF,
        BEN_PRTT_ENRT_RSLT             BPER, 
        BEN_PL_F                       BP, 
        BEN_BENEFIT_RELATIONS_F        BBR
        WHERE PAPF.PERSON_ID               = BPER.PERSON_ID 
        AND     BPER.PL_ID                   = BP.PL_ID 
        AND     BBR.PERSON_ID                = PAPF.PERSON_ID 
AND     BBR.BENEFIT_RELATION_NAME    = 'DFLT'
AND     SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE 
AND     SYSDATE BETWEEN BP.EFFECTIVE_START_DATE   AND BP.EFFECTIVE_END_DATE 
AND     SYSDATE BETWEEN BBR.EFFECTIVE_START_DATE  AND BBR.EFFECTIVE_END_DATE 
)
where rn = 1

If you want to accept tied first place then you would change row_number to rank/dense_rank (it won't matter if you're filtering on rn = 1). One would assume that there would only be one row per person per ENROLMENTCOVSTARTDATE though.