WITH DUPLICATE_EMP AS (
    SELECT
        MIN(REMOVED.FULL_NAME) AS FULL_NAME
        , COUNT(REMOVED.FULL_NAME) AS CNT
    FROM
        (
            SELECT
                REPLACE(EMP_LAST_NAME, ' ', '') || REPLACE(EMP_FIRST_NAME, ' ', '')
                    AS FULL_NAME
            FROM
                EMP
            WHERE
                VALID_FLG = 1   -- 有効
        ) AS REMOVED
    GROUP BY
        REMOVED.FULL_NAME
    HAVING
        CNT > 1
)
, NAME_ORDERED AS (
    SELECT
        EMP_CODE AS CODE
        , EMP_LAST_NAME
        , EMP_FIRST_NAME
        , REPLACE(EMP_LAST_NAME, ' ', '') AS LM_REMOVED
        , REPLACE(EMP_FIRST_NAME, ' ', '') AS FN_REMOVED
    FROM
        EMP
    WHERE
        VALID_FLG = 1   -- 有効
    ORDER BY
        LM_REMOVED ASC
        , FN_REMOVED ASC
        , CODE ASC
)
SELECT
    CODE
    , EMP_LAST_NAME AS SURNAME
    , EMP_FIRST_NAME AS NAME
FROM
    NAME_ORDERED
WHERE
    LM_REMOVED || FN_REMOVED
    IN (
        SELECT
            FULL_NAME
        FROM
            DUPLICATE_EMP
    )
;