There's a request where we need to pick the person with the lowest amount of tasks assigned from each group. Like this:

A new task is received. Group A has 10 employees. Employee ABC has the lowest amount of tasks assigned out of the 10, therefore ABC will be assigned to this newly received task.

My tables have the following structure:

Group    Employee   Task
A        John       Walk the dog
A        Jane       Pet the cat
A        Jane       Feed the chicken
B        Mozart     Play violin
B        Mozart     Play something
B        Bach       Fix piano
C        James      Cook Eggs
C        James      Fry something
C        Emma       Salad
C        Emma       Hummus

If a new task is received for group A, the algorithm would pick John, since he has 1 task only. If a new task is received for group B, the algorithm would pick Back, since he has 1 task only. If a new task is received for group C, how can we pick one using order ASC since they both have 2 tasks?

Anybody has any idea how to do it?

Thank you

CodePudding user response：

We can achieve this using a combination of COUNT() and ROW_NUMBER():

WITH cte1 AS (
    SELECT t.*, COUNT(*) OVER (PARTITION BY "Group", Employee) cnt
    FROM yourTable t
),
cte2 AS (
    SELECT t.*, RANK() OVER (PARTITION "Group" ORDER BY cnt) rnk
    FROM yourTable t
)

SELECT "Group", Employee, Task
FROM cte2
WHERE rnk = 1
ORDER BY "Group", Employee;

The first CTE assigns counts of employee tasks within each group. The second CTE ranks records according to how many tasks each employee has.

CodePudding user response：

Find within the group the employee with the lowest count of tasks and if there are multiple then, unless you have another metric you can use to assign tasks, pick the employee from those with the lowest number of tasks at random:

SELECT Employee
FROM   table_name
WHERE  "GROUP" = 'C'
GROUP BY Employee
ORDER BY COUNT(*) ASC, DBMS_RANDOM.VALUE()
FETCH FIRST ROW ONLY;

Which, for your sample data:

CREATE TABLE table_name ("GROUP", Employee, Task) AS
SELECT 'A', 'John',   'Walk the dog'     FROM DUAL UNION ALL
SELECT 'A', 'Jane',   'Pet the cat'      FROM DUAL UNION ALL
SELECT 'A', 'Jane',   'Feed the chicken' FROM DUAL UNION ALL
SELECT 'B', 'Mozart', 'Play violin'      FROM DUAL UNION ALL
SELECT 'B', 'Mozart', 'Play something'   FROM DUAL UNION ALL
SELECT 'B', 'Bach',   'Fix piano'        FROM DUAL UNION ALL
SELECT 'C', 'James',  'Cook Eggs'        FROM DUAL UNION ALL
SELECT 'C', 'James',  'Fry something'    FROM DUAL UNION ALL
SELECT 'C', 'Emma',   'Salad'            FROM DUAL UNION ALL
SELECT 'C', 'Emma',   'Hummus'           FROM DUAL;

May pick:

fiddle