Re: ORDER BY problem

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.