WITH T AS ( SELECT '채명숙' NM FROM DUAL UNION ALL SELECT '한미희' FROM DUAL UNION ALL SELECT '권용무' FROM DUAL UNION ALL SELECT '권용무' FROM DUAL UNION ALL SELECT '호영성' FROM DUAL UNION ALL SELECT '박영관' FROM DUAL UNION ALL SELECT '채명숙' FROM DUAL ) SELECT A.* FROM (SELECT NM , COUNT(*) CNT FROM T GROUP BY NM ) A ,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 2 ) B WHERE A.CNT >= B.LV ORDER BY CNT , NM
with t( nm ) as (
select '한미희' from dual union all
select '호영성' from dual union all
select '박영관' from dual union all
select '권용무' from dual union all
select '권용무' from dual union all
select '채명숙' from dual union all
select '채명숙' from dual
)
select a.*
,max(rn) over (partition by a.nm order by a.nm)
from
(
select a.nm ,row_number() over (partition by a.nm order by a.nm) rn
from t a
)a
order by max(rn) over (partition by a.nm order by a.nm)
,a.nm
,a.rn
;