select 
MEMBER_CODE as MEMBER,
EMAIL,
REPLACE(
substr(
    EMAIL,
        instr(EMAIL, '@')+1
    ),
'.co.jp', '')
 as DOMAIN
from
MEMBER_MST


order by 
DOMAIN desc,
MEMBER desc
;