Generating a numeric pattern in query result set

I'm looking to add an artificial grouping to my result set. I can't utilize ROW_NUMBER or RANK.

Take the following result from SELECT Col1, Col2 FROM myTbl:

Col1     Col2
----     ----
x        y
x        z
y        x
z        x
x        z
y        x

I want to add a column that will count to 3 and then repeat so I can subdivide the result set if need be:

Divider  Col1     Col2
-------  ----     ----
1         x        y
2         x        z
3         y        x
1         z        x
2         x        z
3         y        x

Of course the easy way would be to loop through the result set with a cursor or temp table and add it on, but when the result set is 4 million rows, performance will be terrible.

Anyone know how to add this Divider column directly into a SELECT statement?

In reality, the query will retrieve the result set ordered by date but I still want the divider to count off in a fixed manner. If I would try to partition and use row_number the sequence would be unpredictable. Suppose I wanted to take the larger result set and spread it out over a set of queues but I want them evenly distributed by date to make sure each queue is processing the most recent data. If I would order by date and subdivide only one of the queues would get the most recent data.

I'm not actually returning the date in the result set; I am only ordering by it.

Based on your comments you will be ordering the results by date. In that case you could use

ROW_Number () over( order by date)

Which would output the row numbers. You could then use the SQL Modulo function, % and perform a %3 which would give you 1,2,0 repeating as the row output which gives you the grouping you want.

StackExchange Database Administrators Q#129550, answer score: 6