Thread: Order by not working
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
Not sure how you select the household > > The result is (only the first column is shown): > > household_name > > "Garcia" > "Armstrong" > "Armstrong" > "Bauer" > "Bauer" > "Berst" > "Berst" > "Minch (xxxx)" > "Berst" > “Besel” but unless you select from the resulting table using again an order by, the rows will be returned in an undetermined order.Such is the nature of the relational model - there is no order you can rely on when selecting, unless you specify it Hth, Peter
Thanks Peter. The listing of the result is from pg-admin 4.30 using view/edit data applied to the household_data table. Inthe past this has always returned the table contents in the ORDR BY sort order. Do I need to specify some preference inpg_admin to guarantee this? Dan > On Feb 16, 2021, at 11:34 AM, Peter Coppens <peter.coppens@datylon.com> wrote: > > Not sure how you select the household > >> >> The result is (only the first column is shown): >> >> household_name >> >> "Garcia" >> "Armstrong" >> "Armstrong" >> "Bauer" >> "Bauer" >> "Berst" >> "Berst" >> "Minch (xxxx)" >> "Berst" >> “Besel” > > > but unless you select from the resulting table using again an order by, the rows will be returned in an undetermined order.Such is the nature of the relational model - there is no order you can rely on when selecting, unless you specify it > > Hth, > > Peter >
What would you tell pgadmin? "Order this particular query -- out of all the billion queries I might write -- in this particular manner?"
No, that's not how things work. Just add an ORDER BY when you query the table.
No, that's not how things work. Just add an ORDER BY when you query the table.
On 2/16/21 12:48 PM, Dan Nessett wrote:
Thanks Peter. The listing of the result is from pg-admin 4.30 using view/edit data applied to the household_data table. In the past this has always returned the table contents in the ORDR BY sort order. Do I need to specify some preference in pg_admin to guarantee this? DanOn Feb 16, 2021, at 11:34 AM, Peter Coppens <peter.coppens@datylon.com> wrote: Not sure how you select the householdThe result is (only the first column is shown): household_name "Garcia" "Armstrong" "Armstrong" "Bauer" "Bauer" "Berst" "Berst" "Minch (xxxx)" "Berst" “Besel”but unless you select from the resulting table using again an order by, the rows will be returned in an undetermined order. Such is the nature of the relational model - there is no order you can rely on when selecting, unless you specify it Hth, Peter
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
Thanks,
Dan
On Feb 16, 2021, at 12:11 PM, Ron <ronljohnsonjr@gmail.com> wrote:What would you tell pgadmin? "Order this particular query -- out of all the billion queries I might write -- in this particular manner?"
No, that's not how things work. Just add an ORDER BY when you query the table.On 2/16/21 12:48 PM, Dan Nessett wrote:Thanks Peter. The listing of the result is from pg-admin 4.30 using view/edit data applied to the household_data table. In the past this has always returned the table contents in the ORDR BY sort order. Do I need to specify some preference in pg_admin to guarantee this? DanOn Feb 16, 2021, at 11:34 AM, Peter Coppens <peter.coppens@datylon.com> wrote: Not sure how you select the householdThe result is (only the first column is shown): household_name "Garcia" "Armstrong" "Armstrong" "Bauer" "Bauer" "Berst" "Berst" "Minch (xxxx)" "Berst" “Besel”but unless you select from the resulting table using again an order by, the rows will be returned in an undetermined order. Such is the nature of the relational model - there is no order you can rely on when selecting, unless you specify it Hth, Peter--
Angular momentum makes the world go 'round.
On Tuesday, February 16, 2021, Dan Nessett <dnessett@yahoo.com> wrote:
Thanks Peter. The listing of the result is from pg-admin 4.30 using view/edit data applied to the household_data table. In the past this has always returned the table contents in the ORDR BY sort order. Do I need to specify some preference in pg_admin to guarantee this?
pgAdmin4 might be keying off of the presence of an index, which this table doesn’t have.
David J.
Thanks to those who responded. I have solved my problem by noting the advice to use a select with order by. In particular, I need to export the data to a csv file anyway, so I use the following copy command:
COPY (SELECT household_name, family_list, street_address, city, state, zip, phone_list, email_list
FROM "household_data"
ORDER BY household_name
)
TO '/tmp/household_data.csv'
WITH (FORMAT CSV, HEADER);
This works.
Regards,
Dan
On Feb 16, 2021, at 12:35 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, February 16, 2021, Dan Nessett <dnessett@yahoo.com> wrote:Thanks Peter. The listing of the result is from pg-admin 4.30 using view/edit data applied to the household_data table. In the past this has always returned the table contents in the ORDR BY sort order. Do I need to specify some preference in pg_admin to guarantee this?pgAdmin4 might be keying off of the presence of an index, which this table doesn’t have.David J.
SQL is only intuitive to people who've done programming... :)
Also, since your table names are only composed of lower case and underscores, the double quotes are not needed.
Also, since your table names are only composed of lower case and underscores, the double quotes are not needed.
On 2/16/21 1:41 PM, Dan Nessett wrote:
Thanks to those who responded. I have solved my problem by noting the advice to use a select with order by. In particular, I need to export the data to a csv file anyway, so I use the following copy command:COPY (SELECT household_name, family_list, street_address, city, state, zip, phone_list, email_listFROM "household_data"ORDER BY household_name)TO '/tmp/household_data.csv'WITH (FORMAT CSV, HEADER);This works.Regards,DanOn Feb 16, 2021, at 12:35 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, February 16, 2021, Dan Nessett <dnessett@yahoo.com> wrote:Thanks Peter. The listing of the result is from pg-admin 4.30 using view/edit data applied to the household_data table. In the past this has always returned the table contents in the ORDR BY sort order. Do I need to specify some preference in pg_admin to guarantee this?pgAdmin4 might be keying off of the presence of an index, which this table doesn’t have.David J.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
On Tue, 2021-02-16 at 16:11 -0600, Ron wrote: > SQL is only intuitive to people who've done programming... :) SQL is quite counter-intuitive to people who have only done procedural programming. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com