Order by not working

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. 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?

Dan

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?

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

--
Angular momentum makes the world go 'round.

Thanks,

Dan

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

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.

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_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 <mailto:david.g.johnston@gmail.com>> wrote:

On Tuesday, February 16, 2021, Dan Nessett <dnessett@yahoo.com
<mailto: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.

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

On 2021-02-17 08:45:05 +0100, Laurenz Albe wrote:

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.

Yes, different paradigm. SQL is more like a functional or logic
programming language (I often thought that Prolog would make a nice
query language for an RDBMS).

It also fell into what I like to call the COBOL trap: Designing a
language so that looks like normal English in the hope that "ordinary
people" will be able to use it. In reality that doesn't help
non-programmers much (it's still a formal language with precise
semantics and the computer will do what you say, not what you mean), but
makes it harder for programmers.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"