I have the following issue i want to limit on the total amount of items returned while limiting one value to a certain number
I do a query like this
x
select * from messages order by m.priority ASC limit 5;
Now I want to do the following, I have a field called running and I want to be able to say that in these results I want to have maximum n results that are in not running, e.g. 2 but I still want to get 5 results back based on the priority
So if I have for example the following data in the DB:
priority | running
----------+---------
1 | true
2 | false
3 | false
4 | false
5 | false
6 | true
7 | true
8 | true
And I have a total limit of 5 and a non-running limit of 2 I expect the following result
priority | running
----------+---------
1 | true
2 | false
6 | true
7 | true
8 | true
Does someone have a suggestion how to do it in PostgreSQL?
Update: I got the query that gives the result that I want based on @Gordon Linoff answer
select m.*
from (select t.*,
row_number() over (partition by t.running order by t.priority) as seqnum
from message t) m
where ((not m.running and seqnum <= 2) or (m.running and seqnum <= 5)) order by m.priority limit 5;
Link http://sqlfiddle.com/#!17/98477/1
Advertisement
Answer
I think you want:
select m.*
from (select m.*,
row_number() over (partition by running order by m.priority) as seqnum
from messages
) m
where ((not m.running and seqnum <= 2) or
(m.running and seqnum <= 3)
);
Here 3 = 5 – 2.