Thread: ORDER BY Problem

Hello...

Why does Postgresql order the uppercase letters first?

I have e.g. a table with one row an in this row there are follow values:

row1
----
ADC
aa
ABC

With this select-syntax

select * from table order by row1

I become this output

ABC
ADC
aa

but I want this ouptut:

aa
ABC
ADC

What do I wrong?

Thanks: Severin Olloz

Severin Olloz <S.Olloz@soid.ch> writes:

> Hello...
>
> Why does Postgresql order the uppercase letters first?

Because that's the way ASCII orders them, I guess.

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan

On Wed, Jun 06, 2001 at 03:17:36AM +0200, Severin Olloz wrote:
> Hello...
>
> Why does Postgresql order the uppercase letters first?

Because all uppercase letters come before the lowercase letters. Maybe
"ORDER BY lower(<column>)" will work?  This should also be locale
dependent ...

--
Eric G. Miller <egm2@jps.net>

On Wed, 6 Jun 2001, Severin Olloz wrote:

> Why does Postgresql order the uppercase letters first?

Because that's how ASCII does it.

If you want non case-sensitive ordering, ORDER BY upper(column)
--
Tod McQuillin

Severin Olloz <S.Olloz@soid.ch> writes:

> Why does Postgresql order the uppercase letters first?

That is locale dependent - if you don't use a proper locale but use
straight ASCII, A...Za...d would be correct. If you set a locale,
you'll get (Aa)(Bb) etc instead, which is the correct way to sort in
human languages.

> I have e.g. a table with one row an in this row there are follow values:
>
> row1
> ----
> ADC
> aa
> ABC
>
> With this select-syntax
>
> select * from table order by row1
>
> I become this output
>
> ABC
> ADC
> aa

Note that "aa" can give you some surprises anyway... e.g. in
Norwegian, "aa" is mapped to "å", the last character in the alphabet
(in Norwegian words, not foreign names etc... this distinction is
obviously rather hard to sort by for computers:).

Note that not using locales also makes "ORDER BY" give other bogus
results - as ASCII (or latin1) doesn't know the local alphabets, it
can't sort according to them. Example, for Norwegian:


create table bar(
    ord varchar(40),
     foo int,
    primary key(ord));

insert into bar values('ære',2);
insert into bar values('åre',3);
insert into bar values('are',4);
insert into bar values('zsh',5);
insert into bar values('begynne',6);
insert into bar values('øve',7);

select ord,foo from bar order by ord;

Here is a valid result:

 are     |   4
 begynne |   6
 zsh     |   5
 ære     |   2
 øve     |   7
 åre     |   3

Here is an invalid result:

 are     |   4
 begynne |   6
 zsh     |   5
 åre     |   3
 ære     |   2
 øve     |   7

The invalid result is what you'll get if you run postgresql without
locale information, so make sure you set one before you initialize the
database and run the postmaster.
--
Trond Eivind Glomsrød
Red Hat, Inc.