Round Robin order in postgresql

Assume we have this table: ( each object can have an instance multiple times but with different timings for example object A has two instances of 1)

  object   | instance | time
-----------------------------
   A       |    1     |  100
   A       |    1     |  99
   A       |    5     |  5
   A       |    5     |  3
   A       |    5     |  4
   A       |    3     |  10
   B       |    9     |  17
   B       |    9     |  18
   B       |    2     |  20

I want to order this table so that the objects are ordered in a round robin way but all the rows with the same instance should come together ordered by time)

The result should be:
    object     | instance | time
    -----------------------------
       A       |    5     |  3
       A       |    5     |  4
       A       |    5     |  5
       B       |    9     |  17
       B       |    9     |  18
       A       |    3     |  10
       B       |    2     |  20
       A       |    1     |  99
       A       |    1     |  100

Answer

Try this:

with cte as 
(select distinct on (object, instance) object,
        instance, 
        rank() over (partition by object order by time)  
        from example order by object, instance, time

)

select 
t1.*
from 
example t1 
left join cte t2 on t1.object=t2.object and t1.instance=t2.instance 
order by t2.rank,t1.object,t1.time

DEMO

Advertisement

Answer