Assigning Rows Round-Robin to Workers With ROW_NUMBER

Round-robin assignment deals work out in turns, the way you deal cards, and ROW_NUMBER does the dealing in one query. No loop, no cursor, no counter column. Number the tasks, number the workers, then match them with a remainder.

Ten tasks and three workers

Picture a support queue at 2 AM. Ten tasks are waiting and three people are on call. Nobody wants to be the one who got the big pile. Priority 1 means urgent. The task ids have gaps because a few tasks were cancelled, which is normal in a real queue. The worker ids are 10, 20 and 30 on purpose, because real ids are rarely tidy.

DROP TABLE IF EXISTS #Assignments;
DROP TABLE IF EXISTS #Tasks;
DROP TABLE IF EXISTS #Workers;
CREATE TABLE #Workers (WorkerId int PRIMARY KEY, WorkerName varchar(10) NOT NULL);
CREATE TABLE #Tasks (TaskId int PRIMARY KEY, Title varchar(30) NOT NULL, Priority tinyint NOT NULL);
CREATE TABLE #Assignments (TaskId int PRIMARY KEY, WorkerName varchar(10) NOT NULL);
INSERT #Workers VALUES (10, 'Asha'), (20, 'Ben'), (30, 'Chen');
INSERT #Tasks VALUES
    (1, 'Reset passwords', 2), (2, 'Archive old reports', 2), (4, 'Fix failed backup', 1),
    (5, 'Disk space alert', 1), (7, 'Rebuild index', 2), (8, 'Update statistics', 2),
    (10, 'Broken login page', 1), (11, 'Deadlock on orders', 1), (13, 'Clean temp files', 2),
    (14, 'Review job history', 2);

Number both sides, then match the remainder

The trick has three steps. Number the tasks from zero in the order you want them dealt. Take that number modulo the worker count, which gives a slot of 0, 1 or 2. Number the workers from zero too, and join on the slot.

Notice the order: priority first, then TaskId. That spreads the urgent tasks across everyone instead of piling them on whoever sorts first. The TaskId tie-breaker also makes the result repeatable.

WITH w AS (
    SELECT WorkerName, ROW_NUMBER() OVER (ORDER BY WorkerId) - 1 AS Slot
    FROM #Workers
),
t AS (
    SELECT TaskId, Title, Priority,
           (ROW_NUMBER() OVER (ORDER BY Priority, TaskId) - 1)
               % (SELECT COUNT(*) FROM #Workers) AS Slot
    FROM #Tasks
)
SELECT t.TaskId, t.Title, t.Priority, w.WorkerName
FROM t
JOIN w ON w.Slot = t.Slot
ORDER BY t.Priority, t.TaskId;

The four urgent tasks go to Asha, Ben, Chen and then Asha again. The turns simply wrap around. Nobody gets two urgent tasks except Asha, and Asha only gets that because four does not divide by three.

Save the deal and check the load

A SELECT only shows a plan. A real queue needs the answer stored. Insert the same result into an assignments table, then count what each worker holds.

WITH w AS (
    SELECT WorkerName, ROW_NUMBER() OVER (ORDER BY WorkerId) - 1 AS Slot
    FROM #Workers
),
t AS (
    SELECT TaskId,
           (ROW_NUMBER() OVER (ORDER BY Priority, TaskId) - 1)
               % (SELECT COUNT(*) FROM #Workers) AS Slot
    FROM #Tasks
)
INSERT #Assignments (TaskId, WorkerName)
SELECT t.TaskId, w.WorkerName
FROM t
JOIN w ON w.Slot = t.Slot;

SELECT a.WorkerName, COUNT(*) AS Tasks,
       SUM(CASE WHEN k.Priority = 1 THEN 1 ELSE 0 END) AS Urgent
FROM #Assignments AS a
JOIN #Tasks AS k ON k.TaskId = a.TaskId
GROUP BY a.WorkerName
ORDER BY a.WorkerName;

Asha holds four tasks, Ben three and Chen three. Ten tasks cannot split evenly three ways, so the gap between the busiest and quietest worker is never more than one. That is the whole promise of round-robin.

Why not modulo on the id, or NTILE

The tempting shortcut is TaskId % 3. It breaks the moment ids have gaps. Every surviving id here leaves a remainder of 1 or 2, so slot 0 never appears and one worker sits idle. ROW_NUMBER counts the rows that exist, not the ids that once existed.

NTILE is the other tool people grab. It cuts the list into contiguous chunks, not turns. Chunk 1 gets the first four tasks in order, and those are exactly the four urgent ones.

SELECT TaskId % 3 AS Slot, COUNT(*) AS Tasks
FROM #Tasks
GROUP BY TaskId % 3
ORDER BY Slot;

SELECT Chunk, COUNT(*) AS Tasks,
       SUM(CASE WHEN Priority = 1 THEN 1 ELSE 0 END) AS Urgent
FROM (SELECT Priority, NTILE(3) OVER (ORDER BY Priority, TaskId) AS Chunk
      FROM #Tasks) AS x
GROUP BY Chunk
ORDER BY Chunk;

Slot 1 and slot 2 hold five tasks each. Slot 0 is missing. With NTILE, chunk 1 holds all four urgent tasks and the other chunks hold none. One unlucky person gets every fire. Use NTILE to split a table into ranges, and use the remainder to deal turns.

A second batch should not restart at Asha

New tasks arrive tomorrow. If you restart the numbering at zero, Asha gets the first new task again, and Asha already holds the extra one. Start the new deal where the last one stopped. The count of tasks already assigned tells you where that is.

INSERT #Tasks VALUES (15, 'Rotate logs', 2), (16, 'Check replication', 1);

WITH w AS (
    SELECT WorkerName, ROW_NUMBER() OVER (ORDER BY WorkerId) - 1 AS Slot
    FROM #Workers
),
t AS (
    SELECT TaskId,
           (ROW_NUMBER() OVER (ORDER BY Priority, TaskId) - 1
            + (SELECT COUNT(*) FROM #Assignments))
               % (SELECT COUNT(*) FROM #Workers) AS Slot
    FROM #Tasks
    WHERE TaskId NOT IN (SELECT TaskId FROM #Assignments)
)
INSERT #Assignments (TaskId, WorkerName)
SELECT t.TaskId, w.WorkerName
FROM t
JOIN w ON w.Slot = t.Slot;

SELECT WorkerName, COUNT(*) AS Tasks
FROM #Assignments
GROUP BY WorkerName
ORDER BY WorkerName;

The urgent task goes to Ben and the other goes to Chen. All three workers now hold four tasks. This offset works while the rotation stays intact. If workers join or leave, count the load per person instead and give the next task to whoever holds the least.

DROP TABLE IF EXISTS #Assignments;
DROP TABLE IF EXISTS #Tasks;
DROP TABLE IF EXISTS #Workers;

Next time a queue feels unfair, check whether the turns are really turns before blaming the people.

Fair assignment is not a loop, it is a remainder.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.