From: | Rory Campbell-Lange <rory(at)campbell-lange(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | array_agg and partition sorts |
Date: | 2013-06-26 09:04:05 |
Message-ID: | 20130626090405.GA917@erota.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)campbell-lange(dot)net
From | Date | Subject | |
---|---|---|---|
Next Message | Jiří Pavlovský | 2013-06-26 09:11:05 | Re: utf8 errors |
Previous Message | Jiří Pavlovský | 2013-06-26 09:03:02 | Re: utf8 errors |