Alban Hertroys <haramrae@xxxxxxxxx> writes: > As stated above, I want to enumerate the runs, starting at 1 and > incrementing by 1 every time a partition from the 'run' window closes, > Is there a way to achieve this through window functions, or do we need to > wrap the thing in a subquery to achieve this? I think this'll work: select datetime, property_A, property_B , first_value(datetime)::time over run as swap_time , dense_rank() over (order by property_A, property_B) , value from process_data window run as (partition by property_A, property_B order by datetime) ; You can't do it with a window function over the "run" window because no window function ever looks outside the current partition. But that's easy to fix by using a different window definition. The planner is smart enough to see that these windows are compatible and only need one sort to be performed. regards, tom lane
