select a.MATERIAL_LIST_ID,a.MATERIAL_name,a.MATERIAL_SORT ,a.MATERIAL_SUB_SORT
from ECPM_MATERIAL_LIST a,ECPM_MATREIAL_TYPE b,(select rownum aaa, type_id,b.type_name from ECPM_MATREIAL_TYPE b
connect by prior type_Id=up_Type_Id start with up_Type_Id=0 order by TYPE_ORDER) c where a.MATERIAL_SUB_SORT=b.TYPE_ID
and c.type_id=b.type_id order by c.aaa, a.MATERIAL_SORT