Mombu the Programming Forum sponsored links

Go Back   Mombu the Programming Forum > Programming > Sorting problem
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 21st April 11:00
jinujosein
External User
 
Posts: 1
Default Sorting problem


hi all,
i am using postgres 7.3.2 .i am converitng a mssql database to postgres.
now i am facing a strange problem. sorting based on a varchar field is not working
as expected. the non alphanumeric characters are not sorting based on the ascii
value of them.

i have the following table structure..

create table accounts
(
AcNo varchar (10),
Name varchar(100),
balance numeric(19,4)
)

when i used the query select * from accounts order by acno. the result is not correct

suppose that the acno field contains values '###1' ,'###2' ,'##10' , '#100'
the sort order in postgres is
'###1'
'##10'
'#100'
'###2'
But i want the result as follows
'###1'
'###2'
'##10'
'#100'

that means the ascii value of # should be considered for sorting..
what is the problem. is it the behaviour of postgres.
do i need to change any configuration. i am using all default configurations
or is it a bug...?
the problem actually is of < & > operators for varchar.

in a simple comparison

select '###2' < '##10'

returns false but i need true.

is there any solution exist. even if i replaced # with any non alphanumeric
character the result is same..

pls help

jinu jose

---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
  Reply With Quote


  sponsored links


2 21st April 11:00
sszabo
External User
 
Posts: 1
Default Sorting problem


What OS and locale are you using? If it's not "C", you're probably
getting bit by the fact that many natural language collations (such as
en_US for example) don't consider most of the symbols except as tie
breakers when sorting. Unfortunately, to change the locale you need to
run initdb again with the appropriate locale, something like:
LANG="C" initdb -D /path/to/dataspace

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
  Reply With Quote
3 21st April 11:01
jllachan
External User
 
Posts: 1
Default Sorting problem


You are obviously not using C locale.
If you can't change it for some reason, you can use:

select * from accounts order by int4( trim( acno, '#'));

JLL


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html
  Reply With Quote
4 30th April 21:25
r.vantwisk
External User
 
Posts: 1
Default Sorting problem


I think what you actually want is natural sorting.

Ries


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org
  Reply With Quote
Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666