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
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 |