Re: BUG #9010: partition by overrides order by in window functions

markella.skempri@onzo.com writes:
> I am trying to get a row_number / rank of a resultset according to a value
> that is ordered by date. However whenever I try to use the order by clause,
> the partition by clause seems to override the ordering and produce false row
> number.

AFAICS, the behavior you're complaining about is exactly what is specified
by the SQL standard.  For one thing, since you've not specified any ORDER
BY at the outer query level, there's no requirement for the rows to be
returned in any particular order.  But the main point is that the
row_number is supposed to be computed within each set of rows having
a distinct value of no_of_gap_days, which the actual output satisfies,
and your wish-list result doesn't.  So I'm pretty sure that you need
to write something else than this in order to get the result you want.

> What I expect to see
> householdid   previous_day  gap_finish  no_of_gap_days  no_of_days
> 1    2011-08-15    2011-08-16    1    1
> 1    2011-08-16    2011-08-17    1    2
> 1    2011-08-17    2011-08-18    1    3
> 1    2011-08-18    2011-08-19    1    4
> 1    2011-08-19    2011-08-20    1    5
> 1    2011-08-20    2011-08-21    1    6
> 1    2011-08-27    2011-08-28    1    7
> 1    2011-08-21    2011-08-27    6    1
> 1    2011-08-28    2011-08-29    1    1
> 1    2011-08-29    2011-08-30    1    2

Unfortunately, I can't help further, because it's not obvious to me what
calculation rule you are wishing for.  I could understand your example
if it was ordered by date, but it isn't.

            regards, tom lane