WITH pre as(
SELECT
    MEMBER_CODE AS MEMBER,
    EMAIL AS EMAIL,
    case instr(EMAIL, '@') when 0 then "" else substring(EMAIL, instr(EMAIL, '@')) end as DOMAIN
FROM
    MEMBER_MST
)
SELECT
    MEMBER,
    EMAIL,
    REPLACE(REPLACE(DOMAIN,'.co.jp', ''),'@','') AS DOMAIN
FROM
    pre
ORDER BY
    DOMAIN DESC,
    MEMBER DESC