How do you restart numbering in a table with rownum?

[Pauline Ioannou]

I'm using table transformer and have a table sorted by duplicate values in one column. I'd like to use rownum to number the columns. However, I'm trying to get it to restart numbering at each new set of values. The over or partition functions are not available. How can this be done with table transformer? Example below of what I'd like output to look like.

1 | apple

2 | apple

1 | orange

2 | orange

3 | orange

[Katerina Kovriga {Stiltsoft} · Accepted solution]

Hi @Pauline Ioannou ,

The Table Transformer macro is based on the AlaSQL library, so the OVER PARTITION functions are not supported.

We can suggest the following workaround (the first screenshot was taken for Server/Data Center, note that for Cloud you'll need the Table Toolbox macro to nest several macros):

As you can see, you'll need three copies of your original table to get the result.

Use the first internal Table Transformer macro to create a standard numeration:

SELECT ROWNUM() as 'N',
T1.'Fruit', T1.'Number'
FROM T1

Use the first internal Pivot Table macro to count the number of the group's entries cumulatively:

Use the second internal Pivot Table macro to count the number of the group's entries as it is:

Now use the external Table Transformer macro to count the required numeration:

SELECT T1.'N' - T2.'Count' + T3.'Count' as 'N',
T1.'Fruit', T1.'Number'
FROM T1 JOIN T* ON T1.'Fruit' = T*.'Fruit'

Hope this may help your case.

[Pauline Ioannou]

Thank you for this! However, the cumulative count option is not available in our pivot macro. 

[Katerina Kovriga {Stiltsoft}]

If the tags of the question don't lie and you are on Cloud, then you definitely have this option.

If you are on Server/Data Center, the only reason you don't see this option is that your version of the Table Filter and Charts for Confluence app is rather old.

The current version is 8.1.1, please update. 

[Pauline Ioannou]

Thanks for that info. I used your methodology with table transformers and it worked! I created a table (using table transformer) with cumulative total column and incremental column. Then, I used your select statement formula to produce the new column. It worked! Thanks so much!!