On 26/06/13, Rory Campbell-Lange (r...@campbell-lange.net) wrote:
> I'm on Postgres 9.1 and I've come across an issue which shows I don't
> understand partition sorting:
> Returns:
>
> -[ RECORD 1
> ]---------------------------------------------------------------------------------
> agg1 |
> {Canberra,Vienna,Baku,Nassau,Dhaka,Porto-Novo,Thimphu,Brasilia,Sofia,Ouagadougou}
> agg2 |
> {Nassau,Ouagadougou,Sofia,Brasilia,Thimphu,Porto-Novo,Dhaka,Baku,Vienna,Canberra}
> agg3 | {Canberra}
> row_number | 1
> -[ RECORD 2
> ]---------------------------------------------------------------------------------
> agg1 | {Bridgetown,Minsk,Brussels,Belmopan,Gaborone}
> agg2 | {Gaborone,Belmopan,Brussels,Minsk,Bridgetown}
> agg3 | {Bridgetown}
> row_number | 1
I've solved this by reading the docs at
http://www.postgresql.org/docs/9.1/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
more carefully, particulary about how sorts affect the frame.
select * from (
select
array_agg(t_name) over (partition by n_group) as agg1
, array_agg(t_name) over (partition by n_group order by
t_additional desc
range between unbounded preceding and
unbounded following) as agg2
, array_agg(t_name) over (partition by n_group order by
t_additional asc
range between unbounded preceding and
unbounded following) as agg3
, row_number() over (partition by n_group)
from test
) x;
produces the desired result:
...
-[ RECORD 10
]--------------------------------------------------------------------------------
agg1 |
{Canberra,Vienna,Baku,Nassau,Dhaka,Porto-Novo,Thimphu,Brasilia,Sofia,Ouagadougou}
agg2 |
{Nassau,Ouagadougou,Sofia,Brasilia,Thimphu,Porto-Novo,Dhaka,Baku,Vienna,Canberra}
agg3 |
{Canberra,Vienna,Baku,Dhaka,Porto-Novo,Thimphu,Brasilia,Sofia,Ouagadougou,Nassau}
row_number | 4
-[ RECORD 11
]--------------------------------------------------------------------------------
agg1 | {Bridgetown,Minsk,Brussels,Belmopan,Gaborone}
agg2 | {Gaborone,Belmopan,Brussels,Minsk,Bridgetown}
agg3 | {Bridgetown,Minsk,Brussels,Belmopan,Gaborone}
row_number | 1
--
Rory Campbell-Lange
r...@campbell-lange.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
