Re: problem with sorting using 'ORDER BY' when character field is filled with numerical values - Mailing list pgsql-general

From Thomas Beutin
Subject Re: problem with sorting using 'ORDER BY' when character field is filled with numerical values
Date
Msg-id 20040521001447.A1289@laokoon.bug.net
Whole thread Raw
In response to problem with sorting using 'ORDER BY' when character field is filled with numerical values  (Dragan Matic <mlists@panforma.co.yu>)
List pgsql-general
On Thu, May 20, 2004 at 05:18:39PM +0200, Dragan Matic wrote:
> If I have a table t with column c which is defined as char(5) and fill
> it with following values:
[...]

> and then do the following: SELECT C FROM T ORDER BY C
> Postgres gives me the following
>
>     1
>    11
>    12
>    14
>     2
[...]

> the same thing done with MS SQL server gives this as a result:
[...]

> which is the result I find more logical, meaning the user would expect
> data sorted this way. Is there some way to make Postgres sort elements
no, the first is the logical if You sort a char(5) field.

> in this way (setting sort order or collation, I suppose)? Tnx in advance
You should try an "ORDER BY to_number(<your_column>, '99999')"

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

pgsql-general by date:

Previous
From: Greg Stark
Date:
Subject: Re: ORDER BY 'DK', 'DE', DESC?
Next
From: Mark Harrison
Date:
Subject: can't get async mode to deliver small batches of rows