4

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.

6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.