How to get the rowNum based on a column value without using group by clause

Question

I have an Oracle table structure that is similar to the following table called my_data

(CHILD, ROOT)

with the following sample data within my_data:

CHILD   ROOT   
-----------------
AA          A                   
AB          A                   
AAA         A                  
BB          B                   
BBB         B                  
BBBA        B                
BBBB        B
C1          C
C2          C
C3          C
C4          C
C5          C
C6          C
C7          C
C8          C

Based on the above sample data I need to produce a SQL query that returns the following information, i.e.:

CHILD   ROOT   ROWNO 
---------------------
AA          A         1          
AB          A         2          
AAA         A         3          
BB          B         1          
BBB         B         2         
BBBA        B         3          
BBBB        B         4 
C1          C         1
C2          C         2
C3          C         3
C4          C         4 
C5          C         5
C6          C         6
C7          C         7
C8          C         8

I tried something like this, but it didn't work.

SELECT ROOT, CHILD, ROWNUM AS ROWNO  
 FROM (
 SELECT PARENT AS PARENT
 , CONNECT_BY_ROOT(PARENT)ROOT 
 , CHILD     
  FROM MY_DATA
 CONNECT BY NOCYCLE PRIOR CHILD = PARENT
 ) A

Appreciate any help !

Answer 1

rownum is a pseudo-column generated when Oracle builds result set. It is always incrementing. What you need is a row_number() function to generate new value for each group. Since you assign ordinal based on prefixes you may assign row number according to the prefix length and an actual value for prefixes of the same length.

with sample_data(CHILD, ROOT) as (

  select 'AA',      'A' from dual union all
  select 'AB',      'A' from dual union all
  select 'AAA',     'A' from dual union all
  select 'BB',      'B' from dual union all
  select 'BBB',     'B' from dual union all
  select 'BBBA',    'B' from dual union all
  select 'BBBB',    'B' from dual union all
  select 'C1',      'C' from dual union all
  select 'C2',      'C' from dual union all
  select 'C3',      'C' from dual union all
  select 'C4',      'C' from dual union all
  select 'C5',      'C' from dual union all
  select 'C6',      'C' from dual union all
  select 'C7',      'C' from dual union all
  select 'C8',      'C' from dual
)
select
  sample_data.*,
  row_number() over(partition by root order by lengthc(child) asc, child asc) as rn
from sample_data

CHILD	ROOT	RN
AA	A	1
AB	A	2
AAA	A	3
BB	B	1
BBB	B	2
BBBA	B	3
BBBB	B	4
C1	C	1
C2	C	2
C3	C	3
C4	C	4
C5	C	5
C6	C	6
C7	C	7
C8	C	8

fiddle