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? -- Gruss: Severin Olloz
> 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 ------ABCDADAbc (3 rows) # select * from test order by upper(col1);col1 ------AbcABCDAD (3 rows)
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 >
"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.
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