Re: How is sorting work?

On May 30, 2014, at 5:13 PM, Quang Thoi <Quang_Thoi(at)symantec(dot)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