CODEWITHSUNDEEP
sqlpostgresql
dknaack
dknaack

Reputation: 60448

PostgreSQL: using PARTITION with LIMIT

I have the following "queue" table

id | type | started_at   | finished_at
----------------------------------
1  | A    | some_date    | some_date
2  | A    | some_date    | NULL
3  | A    | NULL         | NULL
4  | A    | NULL         | NULL

5  | B    | some_date    | some_date
6  | B    | NULL         | NULL
7  | B    | NULL         | NULL
8  | B    | NULL         | NULL

9  | C    | NULL         | NULL
10 | C    | NULL         | NULL
11 | C    | NULL         | NULL
12 | C    | NULL         | NULL

13 | D    | some_date    | NULL
14 | D    | NULL         | NULL
15 | D    | NULL         | NULL
16 | D    | NULL         | NULL

What I want is to get the next items to process.

There should be maximum 2 per type running at the same time.

Because there is already a "running" entry on type A and D, the desired result would look like this.

id | type | started_at   | finished_at
3  | A    | NULL         | NULL

6  | B    | NULL         | NULL
7  | B    | NULL         | NULL

9  | C    | NULL         | NULL
10 | C    | NULL         | NULL

14 | D    | NULL         | NULL

What I have so far is getting the next 2 items per type.

WITH TOSTART AS (
    SELECT *, ROW_NUMBER() 
    over (
        PARTITION BY type
        order by id
    ) AS RowNo 
    FROM table_name
    where started_at is null and finished_at is null
)
SELECT id FROM TOSTART WHERE RowNo <= 2 -- max parallel

What I need is to bring the "status" into account.

Upvotes: 1

Views: 369

Answers (1)

klin
klin

Reputation: 121514

You should first eliminate all finished entries:

select id, type
from (
    select row_number() over (partition by type order by id), *
    from (
        select *
        from my_table
        where finished_at is null
        ) s
    ) s
where started_at is null
and row_number <= 2;

 id | type 
----+------
  3 | A
  6 | B
  7 | B
  9 | C
 10 | C
 14 | D
(6 rows)

Upvotes: 1

Related Questions