0

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

Thom A
  • 88,727
  • 11
  • 45
  • 75
abirami ramachandran
  • 37
  • 6
  • Out of curiosity, what problem necessitates skipping 100s as a solution? I've heard of triskaidekaphobia before, but fear of the number 100 is a new one to me. – Ben Thul Nov 09 '22 at 15:53
  • @BenThul, we are fetching existing record from 1 , so we are trying some prefix. a1,b2,c2.when it enters 100's . We want to get a101,b102,..like that, should skip every 100 for this – abirami ramachandran Nov 09 '22 at 17:26
  • That's... just saying the same thing in a different way. Why is it important that the hundreds get skipped? – Ben Thul Nov 09 '22 at 18:36

1 Answers1

4

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

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • I'm curious why not go with Modulus `%` . Maybe i didn't understood him well ? – Anel Hodžić Nov 09 '22 at 09:06
  • 2
    Because the OP isn't looking to filter the data, @AnelHodžić . That still want row 100, but they want it to be labeled as row 101. – Thom A Nov 09 '22 at 09:07
  • Hi @Larnu, the solution works , is there any way to avoid CTE, and get the value 100,200 skipped with any case condition – abirami ramachandran Nov 09 '22 at 11:44
  • To avoid the CTE you would need to instead use the expression for the column defined as `RN` in the `SELECT` (twice). – Thom A Nov 09 '22 at 11:46
  • @Larnu , I checked this logic is going to 101 after 99,...for 99 ->101, 199->201, 299 ->302,399->403 and so on ..But I want 99->101, 199->201, 299->301 – abirami ramachandran Nov 09 '22 at 14:58
  • @abiramiramachandran that can't work. You have `100=101`, which means that `198=199` and `199 = 201`, but then when you get to `297` you'll then be using `299`; if `299` needs to be `301` what number should `298` be? It can't be `300`, as that's not a permissible value, and it can't be `299` as that's `297`. – Thom A Nov 09 '22 at 15:05
  • @Larnu We just want to skip every 100.. and move to next number. Just sequence should be 99,101,102,103...199,201,202, 203..299,301,302,303,...399,401..We don't want to assign it for pervious values. – abirami ramachandran Nov 09 '22 at 15:48
  • And the above does that, @abiramiramachandran ... So what's the problem? – Thom A Nov 09 '22 at 15:49
  • @Larnu, thanks for your support. Just I'm checking for that logic – abirami ramachandran Nov 09 '22 at 15:51