How is sorting work?

On May 30, 2014, at 5:13 PM, Quang Thoi <Quang_Thoi@symantec.com> wrote:

Any one knows how sorting works?

I am using postgresql 9.3 and runs on Linux machines.
I see different sorting order for the same set of return data.

On linux machines, databases are configured the same.
Database have encoding set to 'utf8' and locale='C'

query:
Select host_id, host_name from host_view order by host_id

hos_id (character varying 128)
host_name (character varying 255)

- On one linux machine (locate in U.S) , the query returned following:

host_id host_name
------------ ------------------
"00017486"; "lnx2.xx.yy.com"
"00017486"; "lnx1.xx.yy.com"

- On a different linux machine (locate in India), the query returned following:

host_id host_name
------------ ------------------
"00017486"; "lnx1.xx.yy.com"
"00017486"; "lnx2.xx.yy.com"

Both results are correct. If you don't specify a sort order, postgresql
will return results in whatever order is convenient - which won't be
consistent from query to query or machine to machine.

You're only sorting by host_id. If you want to sort consistently by
host_id and host_name, so that the sort order is well defined for
identical host_ids, you'll want to do something like

select host_id, host_name from host_view order by host_id, host_name.

Cheers,
Steve

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks Steve!

Just want to get confirmation that postgres does not use any special rules

When no sorting order specified.

Thanks,
Quang.

On 5/30/14 5:20 PM, "Steve Atkins" <steve@blighty.com> wrote:

On May 30, 2014, at 5:13 PM, Quang Thoi <Quang_Thoi@symantec.com> wrote:

Any one knows how sorting works?

I am using postgresql 9.3 and runs on Linux machines.
I see different sorting order for the same set of return data.

On linux machines, databases are configured the same.
Database have encoding set to 'utf8' and locale='C'

query:
Select host_id, host_name from host_view order by host_id

hos_id (character varying 128)
host_name (character varying 255)

- On one linux machine (locate in U.S) , the query returned following:

host_id host_name
------------ ------------------
"00017486"; "lnx2.xx.yy.com"
"00017486"; "lnx1.xx.yy.com"

- On a different linux machine (locate in India), the query returned
following:

host_id host_name
------------ ------------------
"00017486"; "lnx1.xx.yy.com"
"00017486"; "lnx2.xx.yy.com"

Both results are correct. If you don't specify a sort order, postgresql
will return results in whatever order is convenient - which won't be
consistent from query to query or machine to machine.

You're only sorting by host_id. If you want to sort consistently by
host_id and host_name, so that the sort order is well defined for
identical host_ids, you'll want to do something like

select host_id, host_name from host_view order by host_id, host_name.

Cheers,
Steve

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Quang Thoi wrote

Thanks Steve!

Just want to get confirmation that postgres does not use any special rules

When no sorting order specified.

Didn't your testing prove that out sufficiently? I'd be more concerned,
though, if you took random congruence between the two results (I.e. If they
both happened to returned in the same order) to mean that you could trust in
some order the you did not specify as being important.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-is-sorting-work-tp5805636p5805641.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

As your query contains order by on host_id, so it will be sorted only based on host_id. Since in your case host_id is same for both rows, the order in which host_name will be selected will be absolutely random.
If you want query to returns rows sorted on host_name also, then you should add host_name in order by clause as below:

Select host_id, host_name from host_view order by host_id, host_name;
So in this case first it will be sort based on host_id and then on host_name.

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Quang Thoi
Sent: 31 May 2014 05:44
To: pgsql-general@postgresql.org
Subject: [GENERAL] How is sorting work?

Any one knows how sorting works?

I am using postgresql 9.3 and runs on Linux machines.
I see different sorting order for the same set of return data.

On linux machines, databases are configured the same.
Database have encoding set to 'utf8' and locale='C'

query:
Select host_id, host_name from host_view order by host_id

hos_id (character varying 128)
host_name (character varying 255)

- On one linux machine (locate in U.S) , the query returned following:

host_id host_name
------------ ------------------
"00017486"; "lnx2.xx.yy.com"
"00017486"; "lnx1.xx.yy.com"

- On a different linux machine (locate in India), the query returned following:

host_id host_name
------------ ------------------
"00017486"; "lnx1.xx.yy.com"
"00017486"; "lnx2.xx.yy.com"

Thanks,
Quang.