Sequence generation

[lmit]

Hi all,

I have requirement where I have to generate sequence in descending order

lets say have input like

id,name

1,a

2,b

3,c

1,d

1,e

2,f

my output should be like

id,name,seq

1,a,3

1,d,2

1,e,1

2,b,2

2,f,1

3,c,1

i know we can use sequence method which gives acs order but I want in desc order

any help would appreciate

thanks,

lmit

[Anonymous]

After you use the sequence function to generate a sequence id for each row, sort the rows by id and sequence_id column using tSortRow.

[lmit]

Hi @Shicong Hong​ 

thanks for your reply , sort is working but I want the results in a separate column

id,name,seq,order

1,a,1,3

thanks,

lmit

[Anonymous]

You need a more step, do an inner join between two outputs.

one output:

id,name,seq

another output:

id,seq-->sort rows by id and sequence_id in desc order-->id,order

Do an inner join between these two outputs based on id column, and has the output table:

id,name,seq,order

[lmit]

Hi @Shicong Hong​ ,

thanks for your response , but when am doing inner join with unique match getting only one seq like

id;name;seq;order

1;a;1;3

1;b;2;3

and if I do all matches getting duplicates and with duplicates after duplicates I used tunqirow to remove the duplicates but still not getting results as I want

Can you please elaborate on the below step you mentioned

another output:

id,seq-->sort rows by id and sequence_id in desc order-->id,order

you mean to sort the rows with id and seq in desc order ?

can you please help here

thanks,

lmit

[Anonymous · Accepted solution]

sorry, it might be a wrong job design in my previous post, I have created an example Job and test it, it has the output:

 

In this scenario, I need to group the id column and calculate the total number of each group, then generate a sequence id in desc order for each id group, for details, please see the screenshots.

 

Please try and let me know if you have any questions.

Regards

Shong​

[lmit]

Thanks @Shicong Hong​  , it worked

[Anonymous]

great, thanks for your feedback!