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

From: markella(dot)skempri(at)onzo(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #9010: partition by overrides order by in window functions
Date: 2014-01-28 17:52:29
Message-ID: 20140128175229.8089.55168@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 9010
Logged by: Markella Skempri
Email address: markella(dot)skempri(at)onzo(dot)com
PostgreSQL version: 9.3.2
Operating system: Linux Centos 5.5
Description:

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.

My data:
householdid previous_day gap_finish no_of_gap_days
1 2011-08-15 2011-08-16 1
1 2011-08-16 2011-08-17 1
1 2011-08-17 2011-08-18 1
1 2011-08-18 2011-08-19 1
1 2011-08-19 2011-08-20 1
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

my query:

select *, row_number() over (partition by no_of_gap_days order by gap_finish
asc) as no_of_Days from temptable;

the results:
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-28 2011-08-29 1 8
1 2011-08-29 2011-08-30 1 9
1 2011-08-21 2011-08-27 6 1

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

Thanks for your time

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Paul Morie 2014-01-28 19:46:52 Re: BUG #9003: Hard-coding to localhost in postmaster
Previous Message Bruce Momjian 2014-01-28 16:19:00 Re: BUG #9003: Hard-coding to localhost in postmaster