Sorry .. I think you mean if the values are not right padded but you want them ordered that way.
Your example works fine here.. unless you have a locale issue ..
try..
SELECT C FROM T ORDER BY lpad(C,5)
On Thu, 2004-05-20 at 10:49, 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
--
Dave Smith
CANdata Systems Ltd
416-493-9020
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
