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 Apple Inc. 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 10 rows) 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 first name in parentheses). All
through the table there are random insertions of rows that are out of order
with respect to the household_name. This has me stumped. Can anyone give me a
hint of what might be going wrong?
Regards,
Dan Nessett
