SQL: how to use row_number() function to assign the same number for rows with duplicate ids in a repeating format

I have a table with two columns personid and taskid and want to use the ROW_NUMBER function to add a row that counts up to 3 but will duplicate the number as it counts if there are multiple rows for a personid.

The code below is only ordering by personid and repeating after the number 3, but I need it to order by personid and only go to the next number after all the taskid’s for the personid are assigned to one number, or essentially any duplicate personid’s I want to make sure they all only get one number assigned to it.

Select personid, taskid, 1 + ( (row_number() over (order by personid) – 1) % 3) as numberCount from taskTable

Current Table Being Queried From:

PersonId Taskid 
1        1
1        2
1        6
2        3
3        8
3        10
4        9
4        4
4        5
5        7
5        11
5        12

Expected Results After Query:

PersonId Taskid numberCount
1        1      1
1        2      1
1        6      1
2        3      2
3        8      3
3        10     3
4        9      1
4        4      1
4        5      1
5        7      2
5        11     2
5        12     2

Answer

Try this below script using DENSE_RANK –

SELECT *,
(DENSE_RANK() OVER(ORDER BY PersonId)-1)%3 + 1 AS numberCount
FROM your_table

Advertisement

Answer