Re: problem with sorting using 'ORDER BY' when character

Try to cast into an integer.

SELECT C::int4 FROM T ORDER BY C

Just a guess.

Dragan Matic wrote:

> If I have a table t with column c which is defined as char(5) and fill
> it with following values:
>
> insert into t (c) values ('    1')
> insert into t (c) values ('    2')
> insert into t (c) values ('    3')
> insert into t (c) values ('    4')
> insert into t (c) values ('   11')
> insert into t (c) values ('   12')
> insert into t (c) values ('   14')
> insert into t (c) values ('   24')
> insert into t (c) values ('   21')
> insert into t (c) values ('   31')
> insert into t (c) values ('  333')
>
> and then do the following: SELECT C FROM T ORDER BY C
> Postgres gives me the following
>
>    1                11                12
> 14                 2                21                24
>    3                31               333                 4
> the same thing done with MS SQL server gives this as a result:
>
>    1                 2                 3
> 4                11                12                14
> 21                24                31               333
> 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
> in this way (setting sort order or collation, I suppose)? Tnx in advance
>
> Dragan
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> .
>

--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787

There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.