select * from v$version

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

create table t as
select 'Маша' val from dual union all
select '<Тест>' from dual union all
select 'Федя' from dual union all
select 'Маша' from dual union all
select 'John Doe' from dual

select val, count(*) cnt from t group by val order by 1

VAL             CNT
-------- ----------
John Doe          1
Маша              2
<Тест>            1
Федя              1

select row_number() over(order by val) rn, val, cnt from (select val, count(*) cnt from t group by val) order by 1

        RN VAL             CNT
---------- -------- ----------
         3 <Тест>            1
         1 John Doe          1
         2 Маша              2
         4 Федя              1

select row_number() over(order by val) rn, val, cnt from (select val, count(*) cnt from t group by val) order by 1 desc

        RN VAL             CNT
---------- -------- ----------
         4 Федя              1
         3 <Тест>            1
         2 Маша              2
         1 John Doe          1

select row_number() over(order by val desc) rn, val, cnt from (select val, count(*) cnt from t group by val) order by 1

        RN VAL             CNT
---------- -------- ----------
         1 John Doe          1
         2 John Doe          1
         3 John Doe          2
         4 John Doe          1

select row_number() over(order by val desc) rn, val, cnt from (select val, count(*) cnt from t group by val) order by 1 desc

        RN VAL             CNT
---------- -------- ----------
         4 John Doe          1
         3 John Doe          2
         2 John Doe          1
         1 John Doe          1

Попытка номер 1.
select row_number() over(order by val) rn, val, cnt from (select val, count(*) cnt from t group by val) order by 1

        RN VAL             CNT
---------- -------- ----------
         3 <Тест>            1
         1 John Doe          1
         2 Маша              2
         4 Федя              1

Пронумеровано правильно, отсортировано неправильно