I have a column consisting of increasing integers (starts at 1 and increases to N). I want to create a second column that runs from 1, 2, 3, 4 then resets and starts at 1, 2, 3, ... and so on. Explicitly shown, this is what I desire:

1   1
2   2
3   3
4   4
5   1
6   2
7   3
8   4
9   1
10  2
11  3
12  4
13  1
14  2
15  3
... ...

Do you have a hint as to how I can do this in SQL? Ideally a complete code example?

CodePudding user response：

You may try to number the rows with ROW_NUMBER() and add a simple calculation:

SELECT Column1, ((ROW_NUMBER() OVER (ORDER BY Column1) - 1) % 4)   1 AS Column2
FROM Yourtable

CodePudding user response：

If, based on the statement "starts at 1 and increases to N", that the numbers between 1 and N must be continuous, then there's actually no need for ROW_NUMBER at all; you can just apply the logic to your column Column1:

SELECT Column1,
       ((Column1 - 1) % 4)   1 AS SequenceColumn
FROM dbo.YourTable;