Skip to content
Advertisement

SQL query 2 limits

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement