with tmp as (
select
    MEMBER_CODE as MEMBER,
    EMAIL,
    replace(substring(EMAIL, INSTR(EMAIL, '@') + 1), '.co.jp', '') as DOMAIN
from MEMBER_MST
)
select
    *
from tmp
order by
    DOMAIN desc,
    MEMBER desc
;