Luis Ricardo Oliveira Luis Ricardo Oliveira - 16 days ago 4
SQL Question

How to create a new unique identifier for father and children registers where i just have the reading order?

How to create a new unique identifier for father and children registers considering a table structure where I have the first row identified by

C100
and the next rows identified by
C170
?
Table structure:

|C100|0|1|2001118|...
|C170|1|7385|...
|C170|2|15904|...
|C170|3|15975|...
|C170|4|15840|...
|C100|0|1|2015356|...
|C170|1|23194|...
|C100|0|1|2015356|...
|C170|1|20833|...
|C170|2|20836|...
|C170|3|20837|...
|C170|4|20838|...
|C170|5|20839|...
|C170|6|20840|...
|C170|7|20841|...
|C170|8|20842|...
|C170|9|20843|...
|C170|10|20845|...
|C170|11|20846|...


Note: This is an external table created over a formatted file where each row is in sequence by reading.
For register
C170
the second column is ordering by number (1:N), but not the same when register is
C100
, because when register equal to
C100
the next column would be 0 or 1.

To be more elusive,
C100
is the father and each row named
C170
under this
C100
row are the children.
I would like to do something like this:

|1|C100|0|1|2001118|...
|1|C170|1|7385|...
|1|C170|2|15904|...
|1|C170|3|15975|...
|1|C170|4|15840|...
|2|C100|0|1|2015356|...
|2|C170|1|23194|...
|3|C100|0|1|2015356|...
|3|C170|1|20833|...
|3|C170|2|20836|...
|3|C170|3|20837|...
|3|C170|4|20838|...
|3|C170|5|20839|...
|3|C170|6|20840|...
|3|C170|7|20841|...
|3|C170|8|20842|...
|3|C170|9|20843|...
|3|C170|10|20845|...
|3|C170|11|20846|...


Thank you!

Answer

You could try something like this:

select sum(case register when 'C100' then 1 else 0 end) over (order by rownum) ind
     , t1.*
  from Your_Table t1;