Re: order by question

On 08/07/2014 11:36 AM, Kevin Grittner wrote:
> Steve Clark <sclark@netwolves.com> wrote:
>
>> I am confused by how postgres 8,4..13 is sorting my data.
>> select * from test order by data;
>>       data
>> ----------
>>
>>     -
>>     --
>>     1
>>     11
>>     11F
>>     1F
>>     a
>>     b
>>     C
>>     F
>>     -F
>>     Feneric
>>     Generic
>> (14 rows)
>>
>> The first row is a single space, the next row a single -, the next two -- .
>> What I don't understand is why the '-F', the ' Feneric' and
>> the ' Generic' sort where they do.
>>
>> I would expect the output to be like this:
>>
>>       data
>> ----------
>>
>>     Feneric
>>     Generic
>>     -
>>     --
>>     -F
>>     1
>>     11
>>     11F
>>     1F
>>     a
>>     b
>>     C
>>     F
>> (14 rows)
>>     lc_collate
>> -------------
>>     en_US.UTF-8
> PostgreSQL uses the OS collations.  What you are getting matches my
> Ubuntu 14.04 machine:
>
> kgrittn@Kevin-Desktop:~/pg/master$ echo $LANG
> en_US.UTF-8
> kgrittn@Kevin-Desktop:~/pg/master$ sort <<XXX
>>     Feneric
>>     Generic
>>     -
>>     --
>>     -F
>>     1
>>     11
>>     11F
>>     1F
>>     a
>>     b
>>     C
>>     F
>> XXX
>    -
>    --
>    1
>    11
>    11F
>    1F
>    a
>    b
>    C
>    F
>    -F
>    Feneric
>    Generic
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
Hi Kevin,

Thanks for the response. I get the same thing as postgres if I sort from the command line too. But I don't understand
why.

I would expect '-F' to sort immediately after '-' and '--' not after 'F' as
well as ' Feneric' (spaceFeneric) and ' Generic' (spaceGeneric) to sort immediately after ' ' (space).

It is like the space character and the - in -Letter is ignored.


--
Stephen Clark