Limit balancing results by a column

Question

I have a web crawler that have a table with tokens to crawl and each token have a fk_location column that is the website where that token must be processed.

I also have a function that returns me the next 20.000 (order by priority, last_started_execution limit 20000) tokens to search.

My problem is that sometimes the function returns me too much results from the same location and i need this 20.000 balanced by location.

For example: If i have 100.000 tokens in line to process, 5.000 from each location (20 locations in this case), I'll need the function get_next to return me 1.000 from each location, returning the 20.000 limit balanced.

The total line of tokens to process can be huge like millions, but I only need the next 20.000 as balanced as possible.

Also consider that I can have in line few tokens like 30.000 from location A and 2.000 from location B, in this case I will need 18.000 from location A and 2.000 from location B in a total of 20.000 being this the best possible result.

CREATE TABLE search_token (
    id SERIAL,
    search_token varchar(255),
    fk_location integer,
    priority integer,
    last_started_execution timestamp
);

Answer 1

Something like this, then?

SELECT  fk_location, 
        search_token,
        priority,
        ROW_NUMBER() OVER ( 
            PARTITION BY fk_location 
            ORDER BY priority, last_started_execution )
          AS Ranking
FROM    search_token
ORDER BY Ranking, fk_location 
LIMIT 20000;