How to generate same numbers for a group of data

[kiran344]

Hi,
i need to generate same numbers to the set of data in a group.

My Source table holds

PRID TRID
5 2
9 5
10 5
15 7
45 9
89 9
97 9

I need to generate a group id with below data

PRID TRID GroupID
5 2 1
9 5 2
10 5 2
15 7 3
45 9 4
89 9 4
97 9 4


when i used gen_row_num_by_group() i am getting the groupid data in the below table order,but i would like to get the groupID data in the above given table order.

PRID TRID GroupID
5 2 1
9 5 1
10 5 2
15 7 1
45 9 2
89 9 3
97 9 4


Any idea on how to do this.

Thanks

[HerdplattenToni]

gen_row_num() - gen_row_num_by_group() + 1 ?

[kiran344]

Hello Toni,

Thanks for your reply,
do i need to include column ''TRID''

gen_row_num(TRID) - gen_row_num_by_group(TRID) + 1 is it what you are saying wright?

[HerdplattenToni]

gen_row_num() doesn't need a column as parameter, that wouldn't make much sence since it's just enummerating all rows.
gen_row_num_by_group(TRID) is correct though. And you need to sort by TRID in a query before that. But I guess you have figured that out already since you tested that function before your first post.


edit: I should read the examples more carefully. My "Formula" won't give you what you want since the output would be

[ganesan.vijaykumar]

Yes. Generating the sequence id for each group and then, joining back to the main data would be the only the solution, I could think.



Thanks,
VJ

[ganeshxp]

I would think of writing a Custom Function which uses the previous_row_value() function on a sorted source data (Sort Order : TRID, RID)

--> Assign a Counter with value 1
--> Whenever the TRID changes, increment the counter
--> If it don't change by comparing TRID = previous_row_value(TRID) then don't increment

[kiran344]

Hi ganesh,


can you please brief on achiving this using custom function.

[BOBJFan]

Hi Ganesh,

When I am trying to use previous_row_value() function on grouped and sorted data in Custom function, it says we cannot use this function. If you could guide me how I can access previous row value in custom function with or without using Previous_row_value() would be very helpful.


thanks in advance.