473,287 Members | 1,741 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

ORDER BY problem

Hi,

when i do the following query i get an wrong result order on my postgres
system (PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2)

tb_test=# SELECT name, upper(name) FROM tb_wurst ORDER BY upper(name);
name | upper
----------------------------------+----------------------------------
Americabound Tours, Inc. | AMERICABOUND TOURS, INC.
American Airlines | AMERICAN AIRLINES
American Express Tour Operador | AMERICAN EXPRESS TOUR OPERADOR
American Receptive Tours | AMERICAN RECEPTIVE TOURS
American Ring Travel, Inc. | AMERICAN RING TRAVEL, INC.
American Sightseeing Chicago | AMERICAN SIGHTSEEING CHICAGO
AMERICANTOURS INTERNATIONAL INC. | AMERICANTOURS INTERNATIONAL INC.
America West Airlines, Inc. | AMERICA WEST AIRLINES, INC.
(8 rows)

The last line should IMHO be the first; but line 7 is in the correct place
compared to lines 2-6.
Has anybody any hint for me to resolve the problem?

Any help is greatly appreciated.
-tb
--
Thomas Beutin tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
4 1913
On Mon, 1 Mar 2004, Thomas Beutin wrote:
when i do the following query i get an wrong result order on my postgres
system (PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2)

tb_test=# SELECT name, upper(name) FROM tb_wurst ORDER BY upper(name);
name | upper
----------------------------------+----------------------------------
Americabound Tours, Inc. | AMERICABOUND TOURS, INC.
American Airlines | AMERICAN AIRLINES
American Express Tour Operador | AMERICAN EXPRESS TOUR OPERADOR
American Receptive Tours | AMERICAN RECEPTIVE TOURS
American Ring Travel, Inc. | AMERICAN RING TRAVEL, INC.
American Sightseeing Chicago | AMERICAN SIGHTSEEING CHICAGO
AMERICANTOURS INTERNATIONAL INC. | AMERICANTOURS INTERNATIONAL INC.
America West Airlines, Inc. | AMERICA WEST AIRLINES, INC.
(8 rows)

The last line should IMHO be the first; but line 7 is in the correct place
compared to lines 2-6.
Has anybody any hint for me to resolve the problem?


You're probably using a locale such as en_US under which you get an
ordering where spaces nor case are not considered at first IIRC so America
West is America followed by a W, American is America followed by an n.

If you want ordering by the byte values, you'll need to initdb in with the
"C" locale.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2
On Mon, Mar 01, 2004 at 07:28:37AM -0800, Stephan Szabo wrote:
On Mon, 1 Mar 2004, Thomas Beutin wrote:
when i do the following query i get an wrong result order on my postgres
system (PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2)

tb_test=# SELECT name, upper(name) FROM tb_wurst ORDER BY upper(name);
name | upper
----------------------------------+----------------------------------
Americabound Tours, Inc. | AMERICABOUND TOURS, INC.
American Airlines | AMERICAN AIRLINES
American Express Tour Operador | AMERICAN EXPRESS TOUR OPERADOR
American Receptive Tours | AMERICAN RECEPTIVE TOURS
American Ring Travel, Inc. | AMERICAN RING TRAVEL, INC.
American Sightseeing Chicago | AMERICAN SIGHTSEEING CHICAGO
AMERICANTOURS INTERNATIONAL INC. | AMERICANTOURS INTERNATIONAL INC.
America West Airlines, Inc. | AMERICA WEST AIRLINES, INC.
(8 rows)

The last line should IMHO be the first; but line 7 is in the correct place
compared to lines 2-6.
Has anybody any hint for me to resolve the problem?


You're probably using a locale such as en_US under which you get an
ordering where spaces nor case are not considered at first IIRC so America
West is America followed by a W, American is America followed by an n.

If you want ordering by the byte values, you'll need to initdb in with the
"C" locale.

Yes, You're right, my locale at the initdb time was "de_DE@euro", and that's
what i need for the german unlauts. When i change the locale (dig into
localedef et.al.) is it enough to install the new locale stuff and restart
the postmaster or must i dump and reload the whole database?

Greetings from Berlin,
-tb
--
Thomas Beutin tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3
On Mon, 1 Mar 2004, Thomas Beutin wrote:
On Mon, Mar 01, 2004 at 07:28:37AM -0800, Stephan Szabo wrote:
On Mon, 1 Mar 2004, Thomas Beutin wrote:
when i do the following query i get an wrong result order on my postgres
system (PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2)

tb_test=# SELECT name, upper(name) FROM tb_wurst ORDER BY upper(name);
name | upper
----------------------------------+----------------------------------
Americabound Tours, Inc. | AMERICABOUND TOURS, INC.
American Airlines | AMERICAN AIRLINES
American Express Tour Operador | AMERICAN EXPRESS TOUR OPERADOR
American Receptive Tours | AMERICAN RECEPTIVE TOURS
American Ring Travel, Inc. | AMERICAN RING TRAVEL, INC.
American Sightseeing Chicago | AMERICAN SIGHTSEEING CHICAGO
AMERICANTOURS INTERNATIONAL INC. | AMERICANTOURS INTERNATIONAL INC.
America West Airlines, Inc. | AMERICA WEST AIRLINES, INC.
(8 rows)

The last line should IMHO be the first; but line 7 is in the correct place
compared to lines 2-6.
Has anybody any hint for me to resolve the problem?


You're probably using a locale such as en_US under which you get an
ordering where spaces nor case are not considered at first IIRC so America
West is America followed by a W, American is America followed by an n.

If you want ordering by the byte values, you'll need to initdb in with the
"C" locale.

Yes, You're right, my locale at the initdb time was "de_DE@euro", and that's
what i need for the german unlauts. When i change the locale (dig into
localedef et.al.) is it enough to install the new locale stuff and restart
the postmaster or must i dump and reload the whole database?


I think you need to dump any databases in the database cluster, re-initdb
and then reload the databases. However, I'm not sure how that's going to
interact with special characters (since "C" will sort them in their byte
position and "de_DE" ignores spaces).

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #4
On Mon, Mar 01, 2004 at 16:01:35 +0100,
Thomas Beutin <ty****@laokoon.IN-Berlin.DE> wrote:
Hi,

when i do the following query i get an wrong result order on my postgres
system (PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2)

tb_test=# SELECT name, upper(name) FROM tb_wurst ORDER BY upper(name);
name | upper
----------------------------------+----------------------------------
Americabound Tours, Inc. | AMERICABOUND TOURS, INC.
American Airlines | AMERICAN AIRLINES
American Express Tour Operador | AMERICAN EXPRESS TOUR OPERADOR
American Receptive Tours | AMERICAN RECEPTIVE TOURS
American Ring Travel, Inc. | AMERICAN RING TRAVEL, INC.
American Sightseeing Chicago | AMERICAN SIGHTSEEING CHICAGO
AMERICANTOURS INTERNATIONAL INC. | AMERICANTOURS INTERNATIONAL INC.
America West Airlines, Inc. | AMERICA WEST AIRLINES, INC.
(8 rows)

The last line should IMHO be the first; but line 7 is in the correct place
compared to lines 2-6.
Has anybody any hint for me to resolve the problem?
This is a locale issue. You probably are using en_US for the cluster
instead of C. In 7.2 you need to do an initdb to change this.

Any help is greatly appreciated.
-tb
--
Thomas Beutin tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
by: ian justice | last post by:
Before i post actual code, as i need a speedyish reply. Can i first ask if anyone knows off the top of their head, if there is a likely obvious cause to the following problem. For the moment i've...
2
by: One's Too Many | last post by:
Ran into a strange problem today: 8.1.7 on AIX 4.3.3 Database and applications had been working fine for two years and all of a sudden a couple of regularly-run queries are now no longer...
16
by: Bhushit Joshipura | last post by:
This post contains one question and one proposal. A. May I know why order of evaluation of arguments is not specified in C/C++? I asked a question in comp.lang.c++ for the following...
5
by: tilak.negi | last post by:
We have one single hash (#) table, in which we insert data processing priority wise (after calculating priority). for. e.g. Company Product Priority Prod. Qty Prod_Plan_Date C1...
2
by: champ.supernova | last post by:
Hi, I was hoping someone could help me with what I'm sure is a very simple problem...I just can't seem to find the syntax! I'm wanting to update the rows in 'tbl_consolidate' from 'tbl_hold',...
1
by: Jeff Blee | last post by:
I hope someone can help me get this graph outputing in proper order. After help from Tom, I got a graph to display output from the previous 12 months and include the average of that output all in...
4
by: Philippe | last post by:
Hello, I encounter a problem that I cannot solve myself... The problem is the following: I make a table: several records: the first field is always a number, the following field is always...
104
by: Beowulf | last post by:
I have the view below and if I use vwRouteReference as the rowsource for a combo box in an MS Access form or run "SELECT * FROM vwRouteReference" in SQL Query Analyzer, the rows don't come through...
7
by: CDMAPoster | last post by:
I have a form in A97 that requires many textboxes and comboboxes (about 300). The customer insists on seeing all the information on one form so I can't break it up into several forms. Neither...
6
by: p_adib | last post by:
hello. I have 3 files: parent.cpp child.cpp driver.cpp I am working in visual C++ 6.0 and have all the files in one same project. When I ask the IDE to biuld my project, it compiles the code...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.