Re: sorting goofiness

On 1 Apr 2002, Hal Roberts wrote:

> h2o_hroberts=# select ''''||foo||'''' as foo from bar order by foo;
>           foo
> -----------------------
>  'a'
>  'a a'
>  'aaa'
>  'a b'
>  'ab '
>  'abb'
>  'a l'
>  'a l'
>  'alyxbqu lynscw gshi'
>  'a n'
>  'a n'
>  'an'
>  'a nkp'
>  'ann'
>  'a o'
>  'b'
>  'bb b'
>  'bbbb'
> (18 rows)
> ****
>
> "foo" is just a text field.  The determination of whether a string with
> a space at a given location should be sorted before a string with
> non-space at the same location seems to depend on a number of variables
> including the total length of the string and the character following the
> space.  In my elementary school career, I learned that a space should
> always sort before a non-space.
>
> Am I missing something here ?

You're probably using a locale other than C (such as en_US if you're in
the US) which has different sorting rules.  If you put the above data
in a text file and do commands like:
sort file
LANG="C" sort file
LANG="en_US" sort file

You'll see differences in the sort order.