I have a list of agents that I want to assign tasks to using round robin.

agents table:

How to get rows of the table above in a round robin fashion?

Desired outcome:

uuid1 -> uuid2 -> uuid3 -> uuid4 -> uuid1 (repeat)

I tried ordering uuids then selecting the next one based on the previous uuid

SELECT id FROM agents ORDER BY id; // When there is no previous

SELECT id FROM agents WHERE id > 'uuid1' ORDER BY id; // After the first query

But I don't know how to repeat when I reach the last uuid (when uuid4 is retrieved and uuid1 must be selected again)

CodePudding user response：

select nxt
from (
  select
    -- for every id, find the next item, if not exists, use the earliest (min) 
    id, coalesce(lead(id) over (order by id),(select min(id) from Tbl)) as nxt 
  from Tbl
  ) as Agents
where id = 'uuid4'

CodePudding user response：

I don't have PostgreSQL handy right now, but this works under SQL Server and I don't see an obvious reason why it shouldn't under any other DBMS:

COALESCE(
    (SELECT TOP 1 id FROM agents WHERE @current_id < id ORDER BY id),
    (SELECT TOP 1 id FROM agents ORDER BY id)
)

So it simply tries to get the next id (the first argument of COALESCE), and if there is no next id gets the first id (the second argument of COALESCE).

Here is a full T-SQL demo, something similar can probably be done under PostgreSQL...

CREATE TABLE agents  (
    id uniqueidentifier PRIMARY KEY
);

INSERT INTO agents VALUES (NEWID()), (NEWID()), (NEWID()), (NEWID());

DECLARE @current_id uniqueidentifier;

DECLARE @i int = 0;
WHILE @i < 10 BEGIN

    SET @current_id = COALESCE(
        (SELECT TOP 1 id FROM agents WHERE @current_id < id ORDER BY id),
        (SELECT TOP 1 id FROM agents ORDER BY id)
    );

    PRINT CONCAT('@current_id = ', @current_id);

    SET @i = @i   1;

END

CodePudding user response：

You can combine two queries where the second is only run if the first one didn't return anything:

with next_agent as (
  SELECT id
  FROM agents 
  WHERE id > $1 -- the last ID retrieved or NULL if it's the first
  ORDER BY id
  limit 1
)
select *
from next_agent
union all
(
  select *
  from agents
  where not exists (select * from next_agent)
  order by id
  limit 1
)

So if $1 is null (first call) or 'uuid4' the next_agent CTE will not return anything. And in that case the second part of the UNION in the outer query will be run picking the "first" row