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

From: Dragan Matic <mlists(at)panforma(dot)co(dot)yu>
To: pgsql-general(at)postgresql(dot)org
Subject: problem with sorting using 'ORDER BY' when character field is filled with numerical values
Date: 2004-05-20 15:18:39
Message-ID: 40ACCC4F.8050404@panforma.co.yu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)postgresql(dot)org

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dave Smith 2004-05-20 15:21:04 Re: problem with sorting using 'ORDER BY' when character
Previous Message Guy Fraser 2004-05-20 15:15:06 Re: problem with sorting using 'ORDER BY' when character