problem with sorting using 'ORDER BY' when character field is filledwith numerical values

[Dragan Matic]

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 ma*******@postg resql.org

[Guy Fraser]

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 ma*******@postg resql.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.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Dave Smith]

Try ..
SELECT C FROM T ORDER BY ltrim(C)

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 ma*******@postg resql.org

--
Dave Smith
CANdata Systems Ltd
416-493-9020
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Dave Smith]

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 ma*******@postg resql.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

[Tom Lane]

Dragan Matic <ml****@panform a.co.yu> writes:

Is there some way to make Postgres sort elements
in this way (setting sort order or collation, I suppose)?

C locale would sort that way; you appear to be using some other locale.

I concur with the nearby suggestions that you should consider a more
appropriate datatype, if all your data will be integers. Operations on
integers will be lots faster than operations on strings.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Stephan Szabo]

On Thu, 20 May 2004, 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

You are probably running in a collation that doesn't treat spaces as
particularly significant (for example with a locale of en_US). If you
want collation by byte order you can use "C" locale (although you need to
re-initdb to change it).

You could also sort it as numbers by converting to a numeric type first
presumably.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings