Sequence numbering 1

[French79]

I need to create a sequence number for certain rows that are pulled from a raw data file that I'm getting. Here is the situation. I have a data file with about 3 million rows, which I’m able to process into various tables. However, I need to give certain rows a sequence number, basically a Grandparent to Parent to Child relationship.

Here is the scenario, I have 10 rows
5701_012345678_1000 -- This row needs to have a sequence number of 1… Example: 00001
6701_012345678_ABCDE – This row needs to have a its own sequence number of 1 and the number 1 from the previous 5701 row Example: 00001_00001
3102_012345678_4565465afdasfd -- The row needs to be the combination of the first and second row and its own number. Example: 00001_00001_00001
2302 --- Gets none of the sequence numbers
6701_012345678_2000 – This row needs to be a number 2 but still the number 1 from the first 5701 record, since we have not gotten to a new 5701 record; Example: 00001_00002
3102_012345678_ldskfjladjlfdjl --- This row gets the number 2 from the 6701 row above but still the number 1. Example: 00001_00002_00001
5701_987654321_1000 --- This gets the next number of 2. 00002
2302_987654321_dshfkjadhfkl – Nothing
5701_234567890_1000 --- Number 3: 00003
5701_345678901_1000 --- Number 4: 00004
6701_345678901_ABCDE --- Number 1 for it being a new 6701 record under a new 5701 record and the number 4 from the last. Example: 00004_00001

So any row starting with 5701 is the grandparent, if the 5701row has any 6701 (parent) rows, they will follow immediately after and finally if the 6701 row has any 2013 (child) rows they will follow immediately after the 6701 row and then the process could start over with the very next row or 20 rows later.

Any assistance in finding the code that would help me with this is greatly appreciated

 

[Olaf Doschke]

So you say each group of records starting with '5701' is making a grand parent group, starts with a record starting with 5701 and continues until the next '5701'.

That's not impossible but very hard. typically groups of records belonging to the same grand parent record would all point to parent records having the same grandparent reference.

If what you did was accumulating data, not putting primary keys into the result set(s), which you now need to reconstruct, then initially pt those primary keys into result sets and youd have such a numbering.

Besides this very general help I don't get the way you do the numbering. I think if you sort your thoughts and can better describe what you want in a generalised way and not just in an example, you will find the solution yourself.

Perhaps if you describe the real world problem instead of giving cryptic alphanumeric strings, you'll get better responses.

Bye, Olaf.

 

[RyanEK]

How deep can the level records go? If only 3 as per your example then you could write a query for each level. Otherwise this is one of the few examples where a cursor could be utilised.

 

[simian336]

Is your sequence number going to be long enough or can it be longer, Example: 00001 would only be 99,999 rows. You said you had 3 million records...

Simi

 

[French79]

Would never go over that.. Not all the records would need the sequence number

 

[RyanEK]

Try the following:

declare @temp table (id int, data varchar(50), seq varchar(20))
insert into @temp (id,data) values (1, '5701_012345678_1000')
insert into @temp (id,data) values (2, '6701_012345678_ABCDE')
insert into @temp (id,data) values (3, '3102_012345678_4565465afdasfd')
insert into @temp (id,data) values (4, '2302')
insert into @temp (id,data) values (5, '6701_012345678_2000')
insert into @temp (id,data) values (6, '3102_012345678_ldskfjladjlfdjl')
insert into @temp (id,data) values (7, '5701_987654321_1000')
insert into @temp (id,data) values (8, '2302_987654321_dshfkjadhfkl')
insert into @temp (id,data) values (9, '5701_234567890_1000')
insert into @temp (id,data) values (10, '5701_345678901_1000')
insert into @temp (id,data) values (11, '6701_345678901_ABCDE')

;with cte1 as
(select data, right(replicate('0',5) + convert(varchar, row_number() over (order by u.seq)), 5) as seq
 from @temp u where u.data  like '5701%')

update u
set u.seq = c.seq
from @temp u
join cte1 c on u.data = c.data

;with cte2 as
(select data, right(replicate('0',5) + convert(varchar, row_number() over (order by t.seq)), 5) as seq, 
(select top 1 seq from @temp t1 where t1.data like '5701%' and t1.id < t.id order by t1.id desc) as lastseq
from @temp t where data  like '6701%')

update u
set u.seq = c.seq + '_' + c.lastseq
from @temp u
join cte2 c on u.data = c.data

;with cte3 as
(select data, right(replicate('0',5) + convert(varchar, row_number() over (order by t.seq)), 5) as seq, 
(select top 1 seq from @temp t1 where t1.data like '6701%' and t1.id < t.id order by t1.id desc) as lastseq
from @temp t where data  like '3102%')

update u
set u.seq = c.seq + '_' + c.lastseq
from @temp u
join cte3 c on u.data = c.data

select * from @temp

Ryan

 

[French79]

Thanks Ryan. That helped a lot.