Distribute the record in a Round robin method

Question

I have a scenario to get the record in a round-robin way. My table is similar below

Account	Email	Rank	Country
Account_A	abc@account_a.com	1	USA
Account_A	cde@account_a.com	2	USA
Account_A	fgh@account_a.com	3	USA
Account_A	klh@account_a.com	4	USA
Account_B	abc@account_b.com	1	JAPAN
Account_C	xyz@account_c.com	1	INDIA
Account_C	pqr@account_c.com	2	INDIA
Account_D	efg@account_d.com	1	CHINA
Account_D	xyz@account_d.com	2	CHINA
Account_E	abc@account_E.com	1	JAPAN

Expected output: I would need to bring only 8 Records per run. I must bring at least one email from each count. There is a possibility some account can have more than 100 records and some has only 1 record. As you can see the sample output below- One contact from each account from A to E ( total five) then an additional record I need to bring from Rank level 2 `( A, C, & D)

Account	Email	Country
Account_A	abc@account_a.com	USA
Account_A	cde@account_a.com	USA
Account_B	abc@account_b.com	JAPAN
Account_C	xyz@account_c.com	INDIA
Account_C	pqr@account_c.com	INDIA
Account_D	efg@account_c.com	CHINA
Account_D	xyz@account_d.com	CHINA
Account_E	abc@account_e.com	JAPAN

I not sure how to frame the query for this. There is a way we can do it in PL/SQL using for loop statement but i need to achive this through SQL query

Answer 1

You can use the NTILE() function to attribute a new rank to items to help sort them per page, the only limitation is that the parameter must be a constant but you should calculate it based on the total number of accounts to be distributed (in your example: 10):

WITH accounts(Account, Email, Rank, Country) AS (
    SELECT 'Account_A', 'abc@account_a.com', 1, 'USA' FROM DUAL UNION ALL
    SELECT 'Account_A', 'cde@account_a.com', 2, 'USA' FROM DUAL UNION ALL
    SELECT 'Account_A', 'fgh@account_a.com', 3, 'USA' FROM DUAL UNION ALL
    SELECT 'Account_A', 'klh@account_a.com', 4, 'USA' FROM DUAL UNION ALL
    SELECT 'Account_B', 'abc@account_b.com', 1, 'JAPAN' FROM DUAL UNION ALL
    SELECT 'Account_C', 'xyz@account_c.com', 1, 'INDIA' FROM DUAL UNION ALL
    SELECT 'Account_C', 'pqr@account_c.com', 2, 'INDIA' FROM DUAL UNION ALL
    SELECT 'Account_D', 'efg@account_d.com', 1, 'CHINA' FROM DUAL UNION ALL
    SELECT 'Account_D', 'xyz@account_d.com', 2, 'CHINA' FROM DUAL UNION ALL
    SELECT 'Account_E', 'abc@account_E.com', 1, 'JAPAN' FROM DUAL -- UNION ALL
)   
SELECT * FROM (
    SELECT account, email, RANK, 
        country,
    -- the parameter of the ntile() should be the count(*) of the table
    -- divided by the number of rows of each type you want on the page
        ntile(:ntiles) over(ORDER BY account) AS tile
    FROM accounts
)
order by rank, tile, Account
offset (:page_num-1)*:page_size rows fetch next :page_size rows only
;

Page 1:

ACCOUNT  |EMAIL            |RANK|COUNTRY|TILE|
---------+-----------------+----+-------+----+
Account_A|abc@account_a.com|   1|USA    |   1|
Account_B|abc@account_b.com|   1|JAPAN  |   5|
Account_C|xyz@account_c.com|   1|INDIA  |   6|
Account_D|efg@account_d.com|   1|CHINA  |   8|
Account_E|abc@account_E.com|   1|JAPAN  |  10|
Account_A|cde@account_a.com|   2|USA    |   2|
Account_C|pqr@account_c.com|   2|INDIA  |   7|
Account_D|xyz@account_d.com|   2|CHINA  |   9|

Page 2:

ACCOUNT  |EMAIL            |RANK|COUNTRY|TILE|
---------+-----------------+----+-------+----+
Account_A|fgh@account_a.com|   3|USA    |   3|
Account_A|klh@account_a.com|   4|USA    |   4|