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}

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.