select no,count(*) from table group by no order by count(*) desc
select no, count(*) from table group by no order by count(*) desc, no asc
WITH tab AS( SELECT '001' NO FROM dual UNION ALL SELECT '001' FROM dual UNION ALL SELECT '002' FROM dual UNION ALL SELECT '003' FROM dual UNION ALL SELECT '003' FROM dual UNION ALL SELECT '001' FROM dual UNION ALL SELECT '004' FROM dual UNION ALL SELECT '007' FROM dual ) select no, count(1) cnt from tab group by no order by count(1) desc, no ASC NO CNT ---------------- 001 3 003 2 002 1 004 1 007 1
group by no
order by count(*) desc
select no, count(*) from table group by no order by count(*) desc, no asc
SELECT '001' NO FROM dual UNION ALL
SELECT '001' FROM dual UNION ALL
SELECT '002' FROM dual UNION ALL
SELECT '003' FROM dual UNION ALL
SELECT '003' FROM dual UNION ALL
SELECT '001' FROM dual UNION ALL
SELECT '004' FROM dual UNION ALL
SELECT '007' FROM dual
)
select no, count(1) cnt
from tab
group by no
order by count(1) desc, no ASC
NO CNT
----------------
001 3
003 2
002 1
004 1
007 1