Re: Order by and strings

On Tue, Feb 9, 2010 at 1:42 AM, Fredric Fredricson
<Fredric(dot)Fredricson(at)bonetmail(dot)com> wrote:
> Justin Graf wrote:
>
> On 2/8/2010 7:09 PM, Fredric Fredricson wrote:
>
> Hi!
> New to the list with a question that I cannot find the answer to in the
> manual or on the internet but I suspect is trivial. If somebody could point
> me in the correct direction I would be greatful.

It seems you're seeking ASCII or C locale sorting.

> # select * from tmp order by x ;
>    x
> --------
>  -
>  +
>  1
>  -2
>  +3
>  4
>  a
>  aa
>  ---a-b
>  ac
>  -b
>  c
> (12 rows)
>
> In what universe would you expect this sort order? And how to make it
> 'sane'?

In a library perhaps?

> I found a work-around, "order by ascii(x),x", but this continues to baffle
> me.

It's quite simple. en_US locale, and others like it sort by ignoring
things like white space and noise characters so that only letters and
numbers count, and things like ñ sort right near n, not at the end or
beginning of the table.

> It seems to me that if there are any alphanumeric characters in the string
> the rest are ignored in the sort.
> Where did this rule come from?
> I really would appreciate an explanation for this behavior.

It's been around quite some time. I'm afraid I'll have to defer to
some other expert on the exact history.