I have the below query where I need to Select the MAX(COST_DATE) per C.SUBINVENTORY_CODE, but I don’t want to Group On A.REC_TRXN_ID, B.TRANSACTION_ID, A.QUANTITY_ONHAND, or B.TRANSACTION_COST.

(SELECT * FROM 
 (SELECT A.INVENTORY_ITEM_ID
        ,C.SUBINVENTORY_CODE,
        A.REC_TRXN_ID,
        B.TRANSACTION_ID,
        B.COST_DATE,
        A.QUANTITY_ONHAND,
        B.TRANSACTION_COST
        FROM CST_ONHAND_V A,
             CST_ITEM_COST_HISTORY_V B,
             CST_TXN_LAYER_DTLS_V C
        WHERE 1=1
            AND A.INVENTORY_ITEM_ID= :p_inv_num
            AND A.REC_TRXN_ID=B.TRANSACTION_ID
            AND A.COST_ORG_ID = B.COST_ORG_ID
            AND A.COST_BOOK_ID = B.COST_BOOK_ID
            AND A.INVENTORY_ITEM_ID=B.INVENTORY_ITEM_ID                           
            AND A.REC_TRXN_ID=C.REC_TRXN_ID                             
    ORDER BY B.COST_DATE DESC NULLS LAST
    )
    --WHERE ROWNUM = 1
)

Current Results:

INVENTORY_ITEM_ID   SUBINVENTORY_CODE   REC_TRXN_ID TRANSACTION_ID  COST_DATE  QUANTITY_ONHAND  TRANSACTION_COST
100000030624440     11XDC               10727447    10727447        2024-01-23  3        17.4375
100000030624440     41XDC               9532105     9532105         2023-11-05  6        17.43
100000030624440     41XDC               9532105     9532105         2023-11-05  6        17.43
100000030624440     41XDC               9532105     9532105         2023-11-04  6        17.43
100000030624440     11XDC               8790246     8790246         2023-09-18  5      17.4375
100000030624440     11XDC               8659489     8659489         2023-09-08  4      17.4375
100000030624440     11XDC               8659489     8659489         2023-09-08  4      17.4375

Desired Results:

INVENTORY_ITEM_ID   SUBINVENTORY_CODE   REC_TRXN_ID TRANSACTION_ID  COST_DATE  QUANTITY_ONHAND  TRANSACTION_COST
100000030624440     11XDC               10727447    10727447        2024-01-23  3        17.4375
100000030624440     41XDC               9532105     9532105         2023-11-05  6        17.43

Because I have to Group on the fields A.REC_TRXN_ID, B.TRANSACTION_ID, A.QUANTITY_ONHAND B.TRANSACTION_COST that is preventing me from getting just the MAX Cost Date by only Subinventory. How can I achieve this? Thanks in advance.