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.