BUG #9010: partition by overrides order by in window functions
От | markella.skempri@onzo.com |
---|---|
Тема | BUG #9010: partition by overrides order by in window functions |
Дата | |
Msg-id | 20140128175229.8089.55168@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #9010: partition by overrides order by in window functions
Re: BUG #9010: partition by overrides order by in window functions |
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления: