On Tue, Feb 9, 2010 at 1:42 AM, Fredric Fredricson
<Fredric.Fredricson@bonetmail.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.