오라클 그룹내 중복 데이터 에서 하나의 데이터 뽑아내기

DEV/DB

초록매실원액 2017. 7. 3. 17:04

with temp as(

select '1' idx , '10' dept , '20170703' as lastm ,'기타' as data_etc from dual union all

select '2' idx , '20' dept , '20170704' as lastm ,'정상' as data_etc from dual union all

select '2' idx , '20' dept , '20170705' as lastm ,'비정상' as data_etc from dual union all

select '3' idx , '40' dept , '20170703' as lastm ,'기타' as data_etc from dual union all

select '3' idx , '40' dept , '20170701' as lastm ,'기타' as data_etc from dual union all

select '4' idx , '60' dept , '20170703' as lastm ,'기타' as data_etc from dual union all

select '4' idx , '60' dept , '20170702' as lastm ,'기타' as data_etc from dual

)

select *

from (

select a.*

, row_number() over( partition by idx, dept order by lastm desc) rnk

from temp a

)

where rnk = 1

order by idx