Skip every 100th number in Row_Number fucntion in sql-CodePudding

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

CodePudding user response：

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