Order the Names (SQL Puzzle 14)

Problem: A table 'NAMES' has a column 'NAME'. The create and insert scripts are as follows:

CREATE TABLE NAMES
(
NAME VARCHAR2(25)
)

Insert into NAMES (NAME) Values ('DON ');
Insert into NAMES (NAME) Values ('EAGER ');
Insert into NAMES (NAME) Values ('BJ ');
Insert into NAMES (NAME) Values ('BJ ');
Insert into NAMES (NAME) Values ('BJ ');
Insert into NAMES (NAME) Values ('DON ');
Insert into NAMES (NAME) Values ('EAGER ');
Insert into NAMES (NAME) Values ('Gaurav ');
Insert into NAMES (NAME) Values ('Gaurav ');
Insert into NAMES (NAME) Values ('Gaurav ');
Insert into NAMES (NAME) Values ('Nishi ');
Insert into NAMES (NAME) Values ('Gaurav ');
Insert into NAMES (NAME) Values ('Nishi ');
Insert into NAMES (NAME) Values ('XYZ');
Insert into NAMES (NAME) Values ('ABC');
Insert into NAMES (NAME) Values ('Sanjeev');
Insert into NAMES (NAME) Values ('Sanjeev');
Insert into NAMES (NAME) Values ('Sanjeev');
Insert into NAMES (NAME) Values ('PQR');
COMMIT;

Write an SQL to fetch the names in the same order but changed as follows: If a name is unique in the given list, leave the name unchanged. Otherwise, add a single space followed by the names chronological number among all same names. For example if there are 2 Sanjeev's return Sanjeev1 Sanjeev 2. If there is single 'Gaurav', return only Gaurav. The order of the names should be the same as initially given.
In the given case, the result should be:

RESULT
=========
DON 1
EAGER 1
BJ 1
BJ 2
BJ 3
DON 2
EAGER 2
Gaurav 1
Gaurav 2
Gaurav 4
Nishi 1
Gaurav 3
Nishi 2
XYZ
ABC
Sanjeev 1
Sanjeev 2
Sanjeev 3
PQR

Solution:


select
decode(cn,1,name,name' 'row_number() over(partition by name order by name)) result
from
(
select
rn,
name,
count(name) over(partition by name order by name) cn
from
(
select name,rownum rn from names
)
)
order by rn