ORDER BY Problem

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.