X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id DF04DD1B542
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 03:31:26 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 48465-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 00:30:55 -0300 (ADT)
Received: from web14908.mail.yahoo.com (web14908.mail.yahoo.com
	[216.136.225.60])
	by svr1.postgresql.org (Postfix) with SMTP id BEFBAD1B4F4
	for <pgsql-sql@postgresql.org>; Wed, 15 Oct 2003 00:30:54 -0300 (ADT)
Message-ID: <20031015033054.50872.qmail@web14908.mail.yahoo.com>
Received: from [202.9.168.80] by web14908.mail.yahoo.com via HTTP;
	Tue, 14 Oct 2003 20:30:54 PDT
Date: Tue, 14 Oct 2003 20:30:54 -0700 (PDT)
From: "George A.J" <jinujosein@yahoo.com>
Subject: Sorting problem
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-861704606-1066188654=:49479"
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=BAYES_30,
	HTML_20_30
X-Spam-Level: 
X-Archive-Number: 200310/170
X-Sequence-Number: 15570

--0-861704606-1066188654=:49479
Content-Type: text/plain; charset=us-ascii

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
--0-861704606-1066188654=:49479
Content-Type: text/html; charset=us-ascii

<DIV>hi all,</DIV>
<DIV>i am using postgres 7.3.2 .i am converitng a mssql database to postgres.</DIV>
<DIV>now i am facing a strange problem. sorting&nbsp;based on a varchar field is not working</DIV>
<DIV>as expected.&nbsp;the non alphanumeric characters are not sorting based&nbsp;on the ascii </DIV>
<DIV>value of them.</DIV>
<DIV>&nbsp;</DIV>
<DIV>i have the following table structure..</DIV>
<DIV>&nbsp;</DIV>
<DIV>create table accounts</DIV>
<DIV>(</DIV>
<DIV>&nbsp; AcNo varchar (10), </DIV>
<DIV>&nbsp; Name varchar(100),</DIV>
<DIV>&nbsp; balance numeric(19,4)</DIV>
<DIV>)</DIV>
<DIV>&nbsp;</DIV>
<DIV>when i&nbsp;used the query select &nbsp;* from accounts order by acno. the result is not correct</DIV>
<DIV>&nbsp;</DIV>
<DIV>suppose that the acno field contains values '###1' ,'###2' ,'##10' , '#100'</DIV>
<DIV>the sort order in postgres is</DIV>
<DIV>'###1' </DIV>
<DIV>'##10' </DIV>
<DIV>'#100'</DIV>
<DIV>'###2' </DIV>
<DIV>&nbsp;But i want the result as follows</DIV>
<DIV>
<DIV>'###1' </DIV>
<DIV>'###2'</DIV>
<DIV>'##10' </DIV>
<DIV>'#100'</DIV>
<DIV>&nbsp;</DIV>
<DIV>that means the ascii value of # should be considered for sorting..</DIV>
<DIV>what is the problem. is it the behaviour of postgres. </DIV>
<DIV>do i need to change any configuration. i am using all default configurations</DIV>
<DIV>or is it a bug...?</DIV>
<DIV>the problem actually is of &lt; &amp; &gt; operators for varchar.</DIV>
<DIV>&nbsp;</DIV>
<DIV>in a simple comparison </DIV>
<DIV>&nbsp;</DIV>
<DIV>select '###2' &lt; '##10' </DIV>
<DIV>&nbsp;</DIV>
<DIV>returns false but i need true.</DIV>
<DIV>&nbsp;</DIV>
<DIV>is there any solution exist. even if i replaced # with any non alphanumeric </DIV>
<DIV>character the result is same..</DIV>
<DIV>&nbsp;</DIV>
<DIV>pls help</DIV>
<DIV>&nbsp;</DIV>
<DIV>jinu jose</DIV></DIV><p><hr SIZE=1>
Do you Yahoo!?<br>
<a href="http://shopping.yahoo.com/?__yltc=s%3A150000443%2Cd%3A22708228%2Cslk%3Atext%2Csec%3Amail">The New Yahoo! Shopping</a> - with improved product search
--0-861704606-1066188654=:49479--