You can translate the question and the replies:

rownum to reset the generated sequence after a certain number

We need to generate a sequence and then reset the counter after 100. We are able to use rownum function to generate the sequence but it doesn't reset after a certain number. How can we achieve this?
user
26-04-2023 03:07:56 -0400
code

1 Answer

Hi, Similar to SQL, you can use the [module operation](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/numeric_functions/numeric_functions#mod) (%) to set the limit or reset threshold of your generated sequence. Module operation or also called as modulo operator, returns the integer remainder when you perform division on 2 numbers. To reset your ROW_NUMBER() counter every 100, you can write a query like below: SELECT id, customer_name, 1 + ((ROW_NUMBER() OVER (ORDER BY id) - 1) % 100) AS rownum FROM bv_customer Hope this helps!
Denodo Team
28-04-2023 01:18:25 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here