Обсуждение: 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
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.