Count to 3 and loop for each record in query

Transact-SQL
1

Hello,

I'm fairly new to the WHILE Statement.

I have a query that I'd like to add a column to (EmployeeNumber) which will get assigned a value of 1,2,or 3 for each row until the end of the query.

How do I do this?

2

I was able to answer my own question. Below is the example code I used.

DECLARE @site_value INT
DECLARE @counter INT
DECLARE @Rownum INT;
SET @site_value = 0
SET @counter = (SELECT COUNT(ID) FROM Table)
SET @Rownum =0

WHILE @site_value <= @Counter
BEGIN
WHILE @Rownum < 3 and @site_value <= @Counter
BEGIN
SET @site_value = @site_value + 1
Set @Rownum = @Rownum + 1

Print CONVERT(varchar (6), @site_value) + ', ' + CONVERT(varchar(6),@Rownum) + ', ' + Convert(varchar(6),@counter)

END
SET @Rownum = 0
END;

GO

3

Looks like that processes one more row than "SELECT COUNT(ID) FROM Table" ?

The LOOPs are very inefficient in SQL, SET-based statements will perform much better (quite possible 100x better, almost certainly 10x better, probably makes no difference at all for, say, 100 or so rows).

If IDs are contiguous (which is highly unlikely, even if that column is an IDENTITY a rollback etc. would leave a gap in the numbers, as would any Delete) then Modulo arithmetic can give you a 1, 2, 3 value

SELECT ID % 3
FROM MyTable

unfortunately that gives the sequence 1, 2, 0, 1, 2, 0, ... so needs a bit of fiddling to get 1, 2, 3. Typical solution to that is

SELECT (ID - 1 ) % 3 + 1

Assuming that the values are not contiguous then ROW_NUMBER ... OVER can give you the sequence (independent of the ID numbers, but ordered by, say, the IDs)

SELECT	[Rownum] = (ROW_NUMBER()
		OVER
		(
			ORDER BY [ID]
		) - 1) % 3 + 1
	, Col1, Col2, ...	-- Any other required columns
FROM	MyTable

If you are using SQL 2012, or later, then you could use a SEQUENCE object to allocate you a round-Robin sequence of 1, 2, 3 ...