Skip every 100th number in Row_Number fucntion in sql

Question

I have to make a auto increment sequence but it need to skip every 100th number and go to next number.

Example:

1,
2,
3,
... 99,101,102...199,201,202

Could anyone help on this

I tried rownum() over (partition by column_name)+1. But it is updating all row by 1 .

previous_value	After_update
1	2
2	3
99	100

But I want to skip the assignment of 100th row number and go to 101 similarly 200th row number and go to 201

Answer 1

You can use CTE and then some integer maths. This is pseudo SQL, due to the lack of sample data, however, this shold get you where you need:

WITH CTE AS(
    SELECT {Your Columns},
           ROW_NUMBER() OVER (/*PARTITION BY {Column(s)} */ORDER BY {Columns}) AS RN
    FROM dbo.YourTable)
SELECT {Your Columns},
       RN + ((RN-1) / 99) AS RN
FROM CTE;

db<>fiddle