Kindly anyone tell me the logic for oracle sql [duplicate]

Question

User requirement:- 2 policies in each product code with Highest (Maximum) SUM INSURED How do I get 2 policies in each product code with Highest (Maximum) SUM INSURED

SELECT
    PROD.MV_PREMIUM_REGISTER.T_DATE_DESC,
    PROD.MV_PREMIUM_REGISTER.P_POLICY_NUMBER,
    PROD.MV_PREMIUM_REGISTER.P_POLICY_STATUS,
    PROD.MV_PREMIUM_REGISTER.VERSION,
    PROD.MV_PREMIUM_REGISTER.P_RISK_INC_DATE,
    PROD.MV_PREMIUM_REGISTER.P_OFFICE_LOC_ID,
    PROD.MV_PREMIUM_REGISTER.LOCATION_DESC,
    PROD.MV_PREMIUM_REGISTER.ZONE_DESC,
    PROD.MV_PREMIUM_REGISTER.I_IMD_DESC,
    PROD.MV_PREMIUM_REGISTER.P_SUB_IMD,
    PROD.MV_PREMIUM_REGISTER.P_PRODUCT_ID,
    PROD.MV_PREMIUM_REGISTER.P_RISK_EXPIRY_DATE,
    PROD.MV_PREMIUM_REGISTER.P_GC_PLAN,
    PROD.MV_PREMIUM_REGISTER.IMD_CHANNEL,
    PROD.MV_PREMIUM_REGISTER.P_PRODUCT_DESC,
    (PROD.MV_PREMIUM_REGISTER.SUM_INSURED) AS SUM_INSURED,
    (PROD.MV_PREMIUM_REGISTER.GROSS_PREMIUM) AS GROSS_PREMIUM,
    (PROD.MV_PREMIUM_REGISTER.STAMP_DUTY) AS STAMP_DUTY
FROM
    PROD.MV_PREMIUM_REGISTER
WHERE
    T_DATE_DESC BETWEEN '27-FEB-2022' AND '28-FEB-2022'
    AND VERSION NOT LIKE '%E%'
    AND LT_POLICY_YEAR = 1;

Answer 1

SELECT P_PRODUCT_ID,P_POLICY_NUMBER,SUM_INSURED from(
SELECT
PROD.MV_PREMIUM_REGISTER.P_PRODUCT_ID,
PROD.MV_PREMIUM_REGISTER.P_POLICY_NUMBER,
(PROD.MV_PREMIUM_REGISTER.SUM_INSURED) AS SUM_INSURED,
RANK() OVER(PARTITION BY P_PRODUCT_ID ORDER BY SUM_INSURED DESC) AS 
RANK_COUNT
FROM
PROD.MV_PREMIUM_REGISTER
WHERE
T_DATE_DESC BETWEEN '27-FEB-2022' AND '28-FEB-2022'
AND VERSION NOT LIKE '%E%'
AND LT_POLICY_YEAR = 1) MVP where MVP.RANK_COUNT <3;