Complex Sequence Number

Database

Complex Sequence Number

Hi i have a scenario where i need to reset the sequence number with in a particular account sample data is below.

act_no | c2 | c3
--------------------
1 ab bc
1 bc cd
1 cd bb
2 ab db
2 aa bb
1 fc df
2 jh hn

when i load this table into other table with extra column which contains sequence number it should be like this.

act_no | c2 | c3 | Seq
----------------------------
1 ab bc 1
1 bc cd 2
1 cd bb 3
2 ab db 1
2 aa bb 2
1 fc df 4
2 jh hn 3

if you notice clearly the sequence number is unique in act_no and it keeps increasing inside a account. How we can achieve this

Responses are appreciated.

Yamini

3 REPLIES

Re: Complex Sequence Number

Can any one post reply with complete query for my understanding.

thanks
Teradata Employee

Re: Complex Sequence Number

This may be the general approach you are looking for:

INSERT table2
SELECT act_no, c2, c3,
ROW_NUMBER() OVER (PARTITION BY act_no ORDER BY c2) as Seq
FROM table1;

But if you require the exact sequence numbering in your example, I don't see how you achieve that ordering:
2 ab db 1
2 aa bb 2
2 jh hn 3

Re: Complex Sequence Number

Thanks for the query,

Actually i don't require sorting inside like i mentioned in my example,