SQL how to create a count that moves to the next number after a certain number of rows and then loops back to start over

I need to be able to create a column that has a count up to 3 but will provide numbers 1-3 three times each before starting over again.

My current code below only counts up to 3 and starts over. I need it to count each number 3 times then start over

Select personid, taskid, 1 + ( (row_number() over (order by personid) - 1) % 3) AS taskNumber2 from taskTable

Table in DB:

Personid taskid
1        1 
1        2
2        3
2        4
2        5
3        6
4        7
4        8
5        9
5        10
5        11

Expected Results:

Personid taskid numberCount
1        1      1
1        2      1
2        3      1
2        4      2
2        5      2
3        6      2
4        7      3
4        8      3
5        9      3
5        10     1
5        11     1

Solution

You can use row_number to determine your value. Let the row number be x. Since you are repeating ervery 9th entry, take modulo 9. For easier calculating, subtract 1 before this. So now 0-2 -> 1, 3-5 -> 2, 6-8 -> 3. Thus devide by 3 (integer division) and add 1.

SELECT personid, taskid,
    ((row_number() OVER (ORDER BY personid) - 1) % 9) / 3 + 1 AS taskNumber2
FROM taskTable
ORDER BY personid

Also note, that I added the same ORDER BY clause to the whole statement as in the window function, thus assuring correct ordering (at least if this ordering is not ambigious, e.g. it is unique, perhaps a primary key)