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
;