fetching latest record based on two columns in oracle sql

Question

I have data as shown below.

SUBSCRIBER_NO	CUSTOMER_ID	SYS_CREATION_DATE	SYS_UPDATE_DATE	OPERATOR_ID
61686209	199041640	19-JUN-22	19-JUN-22	611316378
61686209	145781645	24-AUG-20	19-JUN-22	611316378
61686210	199044567	19-JUN-22	19-JUN-22	611316379
61686210	145781234	24-AUG-20	17-JUN-22	611316379

from the data above when sys_update_date is same for a subscriber_no column then query should fetch the latest record based on the column sys_creation_date. similarly, when a subscriber_no has two rows have different date in sys_update_date itslef, then it should fetch the row with latest sys_update_date . please help me achieve this in a sql query .

expected output as below.

SUBSCRIBER_NO	CUSTOMER_ID	SYS_CREATION_DATE	SYS_UPDATE_DATE	OPERATOR_ID
61686209	199041640	19-JUN-22	19-JUN-22	611316378
61686210	199044567	19-JUN-22	19-JUN-22	611316379

Answer 1

SELECT SUBSCRIBER_NO, CUSTOMER_ID, SYS_CREATION_DATE, SYS_UPDATE_DATE, OPERATOR_ID
FROM
(
 SELECT 
    SUBSCRIBER_NO, 
    CUSTOMER_ID, 
    SYS_CREATION_DATE, 
    SYS_UPDATE_DATE, 
    OPERATOR_ID, 
    row_number() OVER (PARTITION BY SUBSCRIBER_NO ORDER BY SYS_UPDATE_DATE DESC, SYS_CREATION_DATE DESC) RN
    FROM <table>
) subselect
where subselect.RN=1;