Re: How is sorting work?

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(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Quang Thoi
Sent: 31 May 2014 05:44
To: pgsql-general(at)postgresql(dot)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.