array_agg and partition sorts

[Rory Campbell-Lange <rory@xxxxxxxxxxxxxxxxxx>]

I'm on Postgres 9.1 and I've come across an issue which shows I don't
understand partition sorting:

Given a table like this:

    select * from test;
     n_group |   t_name    | t_additional 
    ---------+-------------+--------------
           1 | Canberra    | Australia
           1 | Vienna      | Austria
           1 | Baku        | Azerbaijan
           1 | Nassau      | The Bahamas
           1 | Dhaka       | Bangladesh
           1 | Porto-Novo  | Benin
           1 | Thimphu     | Bhutan
           1 | Brasilia    | Brazil
           1 | Sofia       | Bulgaria
           1 | Ouagadougou | Burkina Faso
           2 | Bridgetown  | Barbados
           2 | Minsk       | Belarus
           2 | Brussels    | Belgium
           2 | Belmopan    | Belize
           2 | Gaborone    | Botswana

Running this query:

    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) as agg2
            , array_agg(t_name) over (partition by n_group order by t_additional asc) as agg3
            , row_number() over (partition by n_group)
        from test
    ) x
    where
        x.row_number = 1;

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 don't understand why agg3 is missing values.

-- 
Rory Campbell-Lange
rory@xxxxxxxxxxxxxxxxxx


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general