The following query can be used to generate the sequence numbers within a group.
select
GROUP_ID
, GROUP_COUNTER= ROW_NUMBER()
OVER ( PARTITION BY [GROUP_ID]
ORDER BY [GROUP_ID]
)
from GROUP_TABLE
This query can generate a output like the following.
GROUP_ID GROUP_COUNTER
-------------------------------------------
AAA 1
AAA 2
AAA 3
BBB 1
BBB 2
CCC 1
9 comments:
A very good example for assigning a rank within a group and this is something which is most required in a database programming.
To handle such cases,BOBJ provides an in-built function: gen_row_num_by_group
To use this function in BOBJ you just need to pass the column name on which you want to generate the sequence. But it's little tricky while using this method. Make sure you are using this function when your table is completely formed.
Common We are in lazy world :)
Gen_row_num_by_group()....
Just kidding
Anyway nice query
@Ansari, i didn't undertand the tricky part in Gen_row_num_by_group(). The only prerequiste is we shd sort the input field(group field) before using this function.
I think we shd start a blog for Data services.... lol
What u say guys....
Note: Ansasi is excellent webpage designer :)
@Saravana,
I faced this issue while using this function last week. If you have multiple tables (Joining) and wanted to use this function then problem arises. That is the reason I said you need to make sure you table is completely formed before using this function otherwise you will end up having repeated ranks for the same group. I hope you got the point.
In case of opening our Blog for BOBJ, we already have it but it needs to be formally announced. Lets do it today and start posting something on regular basis.
What do you suggest?
If we have, then lets rock...
Regrading function: Agree, we can make group row number only after making the complete table. same database functionality.
After a long time im seeing the post, keep going guys.
Nice and Easy to understand. Thanks
Have you ever considered adding more videos to your blog posts to keep the readers more entertained? I mean I just read through the
entire article of yours and it was quite good but since Im more of a visual learner,I found that to be more helpful well let me know
how it turns out! I love what you guys are always up too. Such clever work and reporting! Keep up the great works guys Ive added you
guys to my blogroll. This is a great article thanks for sharing this informative information.. I will visit your blog regularly for
some latest post.
http://www.sqlservermasters.com/
Thanks for this. It helped me update my table with the right data quickly.
Post a Comment