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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

markella.skempri wrote

1 2011-08-20 2011-08-21 1
1 2011-08-21 2011-08-27 6
1 2011-08-27 2011-08-28 1
1 2011-08-28 2011-08-29 1
1 2011-08-29 2011-08-30 1

1 2011-08-20 2011-08-21 1
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

Yes, partitioning occurs before ordering. The syntax itself suggests that.

Can you explain why you expect the 27-28 row to belong to the "first" group
and not the "third"?

If that is a typo then you are looking for contiguous blocks with the same
gap-size. Once you've identified those blocks you can then partition on
them and performing your row_number.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-9010-partition-by-overrides-order-by-in-window-functions-tp5789343p5789377.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs