All Forums Database
yaminisridhar 3 posts Joined 04/09
04 Apr 2009
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 bc1 bc cd1 cd bb2 ab db2 aa bb1 fc df2 jh hnwhen 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 11 bc cd 21 cd bb 32 ab db 12 aa bb 21 fc df 42 jh hn 3if you notice clearly the sequence number is unique in act_no and it keeps increasing inside a account. How we can achieve thisResponses are appreciated.Yamini

yaminisridhar 3 posts Joined 04/09
04 Apr 2009

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

Fred 1096 posts Joined 08/04
05 Apr 2009

This may be the general approach you are looking for:INSERT table2SELECT act_no, c2, c3, ROW_NUMBER() OVER (PARTITION BY act_no ORDER BY c2) as SeqFROM 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

yaminisridhar 3 posts Joined 04/09
05 Apr 2009

Thanks for the query, Actually i don't require sorting inside like i mentioned in my example,

You must sign in to leave a comment.