Thread: 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

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

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.