Insert rownumber repeatedly in records in t-sql.

Question

Hi,

I want to insert a row number in a records like counting rows in a specific number of range. example output:

RowNumber ID  Name  
1         20   a     
2         21   b
3         22   c
1         23   d
2         24   e
3         25   f
1         26   g
2         27   h
3         28   i
1         29   j
2         30   k

I rather to try using the rownumber() over (partition by order by column name) but my real records are not containing columns that will count into 1-3 rownumber.

I already try to loop each of record to insert a row count 1-3 but this loop affects the performance of the query. The query will use for the RDL report, that is why as much as possible the performance of the query must be good.

any suggestions are welcome. Thanks

Answer 1

have you tried modulo-ing rownumber()?

SELECT ((row_number() over (order by ID)-1) % 3) +1 as RowNumber FROM table