with T as (
    select
    emp_code as CODE
    ,replace(emp_last_name,' ','') as SURNAME
    ,replace(emp_first_name,' ','') as NAME
    ,valid_flg
    from emp
)

select
emp_code as CODE
,emp_last_name as SURNAME
,emp_first_name as NAME
from emp
where emp_code in
(
    select code from T 
    where surname in (
        select surname from T
        where T.valid_flg ='1'
        group by surname,name
        having count(*)>=2
    )
)
order by replace(emp_last_name,' ',''),replace(emp_first_name,' ',''),emp_code