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
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.