I have a query that returns some parent records and child records.
I would like to return for each child records a unique id by increment.
So if i have a record with 3 childs and 1 with 2 childs would like to return.
Parent/Child | Parent_id | Child_id
1. Parent record, Parent_id, 0
2. child_record, Parent_Id, 1
3. child_record, Parent_Id, 2
4. child_record, Parent_Id, 3
5. Parent record, Parent_id, 0
6. child_record, Parent_Id, 1
7. child_record, Parent_Id, 2
Any ideas of how to generate the child_id from 1 and increment by 1 and then reset again for next batch of child records?
This query produces the child numbering contingent to their parent id:
select id_pk -- pk; defines global sort order , p_c -- node type ('parent', 'child') , p_id -- parent id , row_number() over ( partition by p_id order by id_pk ) - 1 c_id -- synthetic child id. from test_pc ;
Adjust the sorting criterion in the order by clause as needed.
Example: (order among children does not matter, just a unique local id is needed)
, row_number() over ( partition by p_id order by p_id, p_c desc ) - 1 c_id