postgresql中的循环顺序

Question

假设我们有这个表：(每个对象可以多次有一个实例，但时间不同，例如对象A有两个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

我想对这个表进行排序，以便以循环方式对对象进行排序，但具有相同实例的所有行都应该按时间排序(

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 1

试试这个：

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

演示

Answer 2

嗯。可以使用WINDOW函数：

order by dense_rank() over (partition by object order by instance),
object