select
    MEMBER_CODE as MEMBER,
    EMAIL,
   substr(EMAIL, INSTR(EMAIL, '@') + 1, INSTR(SUBSTR(EMAIL, INSTR(EMAIL, '@')), '.co.jp') - 2) as DOMAIN
from
  MEMBER_MST
where
  EMAIL like '%co.jp'
order by
  DOMAIN desc,
  MEMBER desc
;