WITH t AS ( SELECT 1 eno, '재임용' app, '20200401' sdt, 1 seq, '20210331' edt FROM dual UNION ALL SELECT 1, '퇴직' , '20190331', 1, '' FROM dual UNION ALL SELECT 1, '계약변경', '20180401', 3, '20190331' FROM dual UNION ALL SELECT 1, '재임용' , '20180401', 2, '20190331' FROM dual UNION ALL SELECT 1, '퇴직' , '20180401', 1, '' FROM dual UNION ALL SELECT 1, '계약변경', '20170401', 2, '20180331' FROM dual UNION ALL SELECT 1, '신규임용', '20170401', 1, '20180331' FROM dual ) SELECT eno, app, sdt, seq, edt , SUBSTR( LAST_VALUE(DECODE(app, '퇴직', sdt)) IGNORE NULLS OVER (PARTITION BY eno ORDER BY sdt DESC, seq DESC) , 1, 6) rym FROM t WHERE eno = 1 ORDER BY sdt DESC ;