Take the max from sub-query using group by and in outer query set equal (=) condition of max_date
SELECT T1.ACC_ID, T1.DATE AS MAX_DATE, T1.COL1, T1.COL2
FROM EMP T1,
(SELECT ACC_ID, MAX(DATE) AS MAX_DATE_I FROM EMP GROUP BY ACC_ID) T2
WHERE T1.ACC_ID = T2.ACC_ID
AND T1.MAX_DATE = T2.MAX_DATE_I
AND ACTIVE_FLAG = 'Y';--===============================================================
SELECT T1.DEPTNO, T1.HIREDATE
FROM EMP T1,
(SELECT DEPTNO, MAX(HIREDATE) AS MAX_DATE FROM EMP GROUP BY DEPTNO) T2
WHERE T1.DEPTNO = T2.DEPTNO
AND T1.HIREDATE = T2.MAX_DATE;
