473,320 Members | 1,921 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

problem with sorting using 'ORDER BY' when character field is filledwith 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 ma*******@postgresql.org

Nov 23 '05 #1
5 1834
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: jwedel_stolo | last post by:
Hi I'm creating a dataview "on the fly" in order to sort some data prior to writing out the information to a MS SQL table I have used two methods in order to determine the sort order of the...
12
by: pmud | last post by:
Hi, I am using teh following code for sorting the data grid but it doesnt work. I have set the auto generate columns to false. & set the sort expression for each field as the anme of that...
8
by: Saputra | last post by:
Does anyone know how to sort a data view numerically? By default, when you sort a field from a table in a database, it sorts it in alpha-numerical order. In MS Access, sort is by alpha-numeric,...
4
by: Deborah V. Gardner | last post by:
I have a field with values like this CO 03-10 CO 03-4 VI 03-8 CO 03-533 I would like these to sort for a report by the first two letters and the digits after the hyphen (-) like this
6
by: Chuck M | last post by:
Is there a simple way to change the sort order in Access (numeric-alpha) to alphanumeric? I've searched the help files and some books, but found no answer. Thanx for any advice Chuck
19
by: Owen T. Soroke | last post by:
Using VB.NET I have a ListView with several columns. Two columns contain integer values, while the remaining contain string values. I am confused as to how I would provide functionality to...
1
by: Dragan Matic | last post by:
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...
2
by: jediknight | last post by:
Hi, I have a listview which has columns of text and columns of numerical data. I need to be able to sort these columns into ascending/desending order whenever the user clicks on the column...
9
by: apattin | last post by:
HI all, Can someone explain this sorting issue? we are using V8 on Windows, but database *might* have been created with V7 (I can find out if it really matters) I have a table with one...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.