The following bug has been logged on the website:
Bug reference: 9010
Logged by: Markella Skempri
Email address: markella.skempri@onzo.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