Order by not working

Поиск
Список
Период
Сортировка
От Dan Nessett
Тема Order by not working
Дата
Msg-id 8D049DD1-DC0D-498B-B7C1-773C4FB82B22@yahoo.com
обсуждение исходный текст
Ответы Re: Order by not working  (Peter Coppens <peter.coppens@datylon.com>)
Список pgsql-general
Hello,

I am using "PostgreSQL 9.6.5 on x86_64-apple-darwin, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on
AppleInc. build 5658) (LLVM build 2336.11.00), 64-bit" 

I am having trouble with a create select statement’s order by clause. The input table, “household_complete_data", (1st
10rows) looks like this (data hidden for privacy): 

household_name,    first_name,    street_address,    city,        state,    zip,        home_phone,    home_email,
cell,       personal_email_primary,    personal_email_secondary 

"Armstrong”        "xxxx”        "xxxxx”        "xxxx”    "xxxx”    "xxxx"
"Armstrong”        "xxxx”        "xxxx”        "xxxx”    "xxxx”    "xxxx”        "xxxx"        "xxxx”
    "xxxx" 
"Bauer”            "xxxx”        "xxxx”        "xxxx”    "xxxx”    “xxxx”        “xxxx"
"Bauer”            "xxxx”        "xxxx”        "xxxx”    "xxxx”    "xxxx”        "xxxx”        "xxxx”
    "xxxx" 
"Berst”            "xxxx”        "xxxx”        "xxxx”    "xxxx”    "xxxx”        "xxxx"
"Berst”            "xxxx”        "xxxx”        "xxxx”    "xxxx”    "xxxx”        "xxxx"
"Berst”            "xxxx”        "xxxx”        "xxxx”    "xxxx”    "xxxx”        "xxxx"
"Berst”            "xxxx”        "xxxx”        "xxxx”    "xxxx”    "xxxx”        "xxxx”        "xxxx”
    "xxxx" 
"Berst”            "xxxx”        "xxxx”        "xxxx”    "xxxx”    "xxxx”        "xxxx"        “xxxx”
    "xxxx" 
"Berst”            "xxxx”        "xxxx”        "xxxx”    "xxxx”    "xxxx2”        xxxx”                “xxxx"

To this table I apply the following SQL statement:

CREATE TABLE "household_data" AS
SELECT household_name,
string_agg(household_complete_data.first_name, ', ') AS family_list,
street_address, city, state, zip,
string_agg(COALESCE(household_complete_data.home_phone, '') || ',' ||     COALESCE(household_complete_data.cell, ''),
',')AS phone_list,  
string_agg(COALESCE(household_complete_data.home_email, '') || ',' ||
COALESCE(household_complete_data.personal_email_primary,'') || ',' ||
COALESCE(household_complete_data.personal_email_secondary,''), ',') AS email_list 
FROM "household_complete_data"
GROUP BY household_name, street_address, city, state, zip
ORDER BY household_name;

The result is (only the first column is shown):

household_name

"Garcia"
"Armstrong"
"Armstrong"
"Bauer"
"Bauer"
"Berst"
"Berst"
"Minch (xxxx)"
"Berst"
“Besel"

The ORDER BY clause doesn’t seem to work properly (note: “Minch (xxxx)” is an entry for the household name that has the
firstname in parentheses). All through the table there are random insertions of rows that are out of order with respect
tothe household_name. This has me stumped. Can anyone give me a hint of what might be going wrong? 

Regards,

Dan Nessett


В списке pgsql-general по дате отправления:

Предыдущее
От: Philip Semanchuk
Дата:
Сообщение: Re: pg_stat_user_tables.n_mod_since_analyze persistence?
Следующее
От: Paul van der Linden
Дата:
Сообщение: Slow index creation