CODEWITHSUNDEEP
t-sql
jeff
jeff

Reputation: 446

Insert rownumber repeatedly in records in t-sql

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

Upvotes: 0

Views: 1302

Answers (1)

Dercsár
Dercsár

Reputation: 1694

have you tried modulo-ing rownumber()?

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

Upvotes: 1

Related Questions