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'
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:
------------ ------------------
"00017486"; "lnx2.xx.yy.com"
"00017486"; "lnx1.xx.yy.com"
- On a different linux machine (locate in India), the query returned following:
------------ ------------------
"00017486"; "lnx1.xx.yy.com"
"00017486"; "lnx2.xx.yy.com"