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?

-- 
Gruss: Severin Olloz

Wed, 6 Jun 2001 02:55:50 +0200
Severin Olloz <S.Olloz@soid.ch>
ORDER BY Problem...
Severin Olloz wrote:

> Why does Postgresql order the uppercase letters first?

Do you have any LOCALE configuration in place?

-- 
Alessio F. Bragadini		alessio@albourne.com
APL Financial Services		http://village.albourne.com
Nicosia, Cyprus		 	phone: +357-2-755750

"It is more complicated than you think"
		-- The Eighth Networking Truth from RFC 1925

Wed, 06 Jun 2001 11:47:44 +0300
Alessio Bragadini <alessio@albourne.com>
Re: 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?

This will not solve your problem, but a way around this is to sort on upper
(row1):

# select * from test order by col1;
 col1
------
 ABCD
 AD
 Abc
(3 rows)

# select * from test order by upper(col1);
 col1
------
 Abc
 ABCD
 AD
(3 rows)



Wed, 6 Jun 2001 14:29:25 +0200 (CEST)
"Reinoud van Leeuwen" <reinoud@xs4all.nl>
Re: ORDER BY Problem...
As far as I know, this is the standard (ASCII-ordered) way of sorting text.
For example, MySQL does the same thing.

Chris

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Severin Olloz
> Sent: Wednesday, 6 June 2001 8:56 AM
> To: pgsql-hackers@postgresql.org
> Subject: [HACKERS] 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?
>
> --
> Gruss: Severin Olloz
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


Thu, 7 Jun 2001 10:10:12 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: ORDER BY Problem...
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

> As far as I know, this is the standard (ASCII-ordered) way of sorting text.

No, it's the "we don't know anything about text, but we can compare
their numeric values" approach.  

-- 
Trond Eivind Glomsrï¿½d
Red Hat, Inc.

06 Jun 2001 22:37:57 -0400
teg@redhat.com (Trond Eivind =?iso-8859-1?q?Glomsr=F8d?=)
Re: ORDER BY Problem...
Christopher Kings-Lynne wrote:
> 
> As far as I know, this is the standard (ASCII-ordered) way of sorting text.
> For example, MySQL does the same thing.
> 

Actually it seems that Severin is using the ASCII locale instead of
en_US or 
some other case-insensitive one.

> >
> > but I want this ouptut:
> >
> > aa
> > ABC
> > ADC
> >
> > What do I wrong?
> >

-----------
Hannu

Thu, 07 Jun 2001 08:09:18 +0500
Hannu Krosing <hannu@tm.ee>
Re: ORDER BY Problem...