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 should set 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: 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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
