By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,993 Members | 1,947 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,993 IT Pros & Developers. It's quick & easy.

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

P: n/a
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*******@postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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*******@postgresql.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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2

P: n/a
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*******@postgresql.org

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

Nov 23 '05 #3

P: n/a
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*******@postgresql.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

Nov 23 '05 #4

P: n/a
Dragan Matic <ml****@panforma.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*******@postgresql.org

Nov 23 '05 #5

P: n/a
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

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.