Table
Id SomeValue
1 Header1
4 Header9
7 Header5
8 Header2
9 Header5
12 Header4
13 Header9
14 Header7
Expected output
Id SomeValue Counter
1 Header1 1
4 Header9 2
7 Header5 3
8 Header2 4
9 Header5 5
12 Header4 1
13 Header9 2
14 Header7 3
Current output: sqlfiddle. As you can see in fiddle, the counter is fine but it doesn't reset the count after 5 items.
You can use the modulo operator (%) to reset the count. You just need to explicitly treat zeros:
SELECT Id, SomeValue, CASE counter WHEN 0 THEN 5 ELSE counter END
FROM (SELECT Id, SomeValue, ROW_NUMBER() OVER (ORDER BY Id) % 5 AS counter
FROM Table1) t
SQLFiddle with your data attached.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments