WITH t AS ( SELECT '0043' id, 1 hist_seq, 'H0200' ci_id, 'test123' name, '사용' status FROM dual UNION ALL SELECT '0044', 1, 'H0202', 'test323', '사용' FROM dual UNION ALL SELECT '0044', 2, 'H0202', 'test323', '미사용' FROM dual UNION ALL SELECT '0045', 1, 'H0202', 'test323', '사용' FROM dual UNION ALL SELECT '0045', 2, 'H0202', 'test323', '미사용' FROM dual UNION ALL SELECT '0045', 3, 'H0202', 'test324', '미사용' FROM dual ) SELECT id , hist_seq , ci_id , DECODE(ci_id , ci_id_1 , 'N', 'Y') ci_id_yn , name , DECODE(name , name_1 , 'N', 'Y') name_yn , status, DECODE(status, status_1, 'N', 'Y') status_yn FROM (SELECT id, hist_seq, ci_id, name, status , ROW_NUMBER() OVER(PARTITION BY id ORDER BY hist_seq DESC) rn , LAG(ci_id , 1, ci_id ) OVER(PARTITION BY id ORDER BY hist_seq) ci_id_1 , LAG(name , 1, name ) OVER(PARTITION BY id ORDER BY hist_seq) name_1 , LAG(status, 1, status) OVER(PARTITION BY id ORDER BY hist_seq) status_1 FROM t ) WHERE rn = 1 ;