Thread: Re: problem with sorting using 'ORDER BY' when character field is filled with numerical values

Is there a reason you're using char(5) in that column?  If all of the data in that column are going to be numbers, you
shouldset it as an int().  Of course if you need it to be char(5), there is a solution.   

More work:

Create another column d, make it an int(), and put the same data in d that you put in c.  Then sort on d.

Less work:

select c from t order by int8(c);



-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Dragan Matic
Sent: Thursday, May 20, 2004 10:50 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] problem with sorting using 'ORDER BY' when character
field is filled with numerical values


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