This can be implemented in ‘n’ number of ways. But today will see how to implement this using analytical functions.

SELECT DISTINCT A.* FROM (Select ID,MAX(DATE1) OVER (PARTITIONBY ID ORDERBY ID) AS MAX_DATE1,FIRST_VALUE(FLAG1) OVER (PARTITION BY ID ORDER BY DATE1 DESC) AS MAX_DATE_OF_FLAG1,MAX(DATE2) OVER (PARTITION BY ID ORDER BY ID) AS MAX_DATE2,FIRST_VALUE(FLAG2) OVER (PARTITION BY ID ORDER BY DATE2 DESC) AS MAX_DATE_OF_FLAG2FROM CODE_XREF ) A