with sub as ( 
    -- 同姓同名が2件以上存在するデータを抽出
    select
        EMP_CODE
        ,replace (EMP_LAST_NAME || EMP_FIRST_NAME, ' ', '') as EMP_NAME 
    from EMP 
    where VALID_FLG = '1' 
    group by EMP_NAME 
    having count(*) >= 2
    ) 
select
    EMP.EMP_CODE as CODE
    ,EMP.EMP_LAST_NAME as SURNAME
    ,EMP.EMP_FIRST_NAME as NAME
from 
    EMP
    inner join sub
        on replace(EMP.EMP_LAST_NAME || EMP.EMP_FIRST_NAME, ' ', '') = sub.EMP_NAME
where VALID_FLG = '1'
order by
    replace(EMP.EMP_LAST_NAME || EMP.EMP_FIRST_NAME, ' ', '')  asc
    ,CODE asc