with temp as (
select 
    member_code as MEMBER
    ,email as EMAIL
    ,SUBSTR(email, instr(email,'@')+1, 50)AS DOMAIN 
from
    member_mst
)
select
    member
    ,email
    ,substr(domain,1,instr(domain,'.co.jp')-1)as DOMAIN
from temp
order by 
domain desc
,member desc