PostgreSQL: using PARTITION with LIMIT

Question

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.

klin · Accepted Answer · 2018-03-08 18:40:35Z

1

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)

edited Mar 8, 2018 at 18:40

answered Mar 8, 2018 at 17:56

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

dknaack Over a year ago

I would like to use partition because I need to have the max parallel option to be variable. As far as i can see, your answer always response with 1 per type. Sorry for not being clear enough.

dknaack Over a year ago

Thanks but this does not retrieve all desired entries.

klin Over a year ago

Well, your desired result is illogical, why there are two entries for type D?

dknaack Over a year ago

Your right, my mistake. My answer is updated. Thank you very much!

klin Over a year ago

Ok, we finally got along I suppose.

Collectives™ on Stack Overflow

PostgreSQL: using PARTITION with LIMIT

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related