Author |
Message |
kiran344 Principal Member


Joined: 12 Apr 2010
         Posts: 197

|
Posted: Wed Jul 04, 2012 4:38 am Post subject: How to generate same numbers for a group of data |
|
|
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 |
|
Back to top |
|
 |
HerdplattenToni Principal Member


Joined: 13 May 2011
        Posts: 161

|
Posted: Wed Jul 04, 2012 8:43 am Post subject: Re: How to generate same numbers for a group of data |
|
|
gen_row_num() - gen_row_num_by_group() + 1 ? |
|
Back to top |
|
 |
kiran344 Principal Member


Joined: 12 Apr 2010
         Posts: 197

|
Posted: Wed Jul 04, 2012 8:52 am Post subject: Re: How to generate same numbers for a group of data |
|
|
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? |
|
Back to top |
|
 |
HerdplattenToni Principal Member


Joined: 13 May 2011
        Posts: 161

|
Posted: Thu Jul 05, 2012 2:00 am Post subject: Re: How to generate same numbers for a group of data |
|
|
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 Code: | RID TRID GroupID
5 2 1
9 5 2
10 5 2
15 7 4
45 9 5
89 9 5
97 9 5 |
If you want to get to your solution youll need to Split your input, do a distinct TRID and gen_row_num() as output in on one branch and join back to the other branch on TRID |
|
Back to top |
|
 |
ganesan.vijaykumar Forum Member


Joined: 09 Sep 2009
          Posts: 14

|
Posted: Fri Jul 06, 2012 9:17 am Post subject: Re: How to generate same numbers for a group of data |
|
|
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 |
|
Back to top |
|
 |
ganeshxp Forum Addict


Joined: 17 Jul 2008
            Posts: 3700 Location: Chicago, Illinois, USA

|
Posted: Fri Jul 06, 2012 11:18 am Post subject: Re: How to generate same numbers for a group of data |
|
|
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 _________________ Cheers
Ganesh |
|
Back to top |
|
 |
kiran344 Principal Member


Joined: 12 Apr 2010
         Posts: 197

|
Posted: Mon Jul 16, 2012 4:00 am Post subject: Re: How to generate same numbers for a group of data |
|
|
Hi ganesh,
can you please brief on achiving this using custom function. |
|
Back to top |
|
 |
BOBJFan Principal Member


Joined: 24 Sep 2011
        Posts: 139

|
Posted: Wed Aug 28, 2019 4:34 am Post subject: Re: How to generate same numbers for a group of data |
|
|
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. |
|
Back to top |
|
 |
|