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 6888ED1B50D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 16 Oct 2003 06:28:05 +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 34819-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 16 Oct 2003 03:27:35 -0300 (ADT)
Received: from jongert.nl (jongert.nl [194.109.59.87])
	by svr1.postgresql.org (Postfix) with ESMTP id 13D6ED1B502
	for <pgsql-sql@postgresql.org>; Thu, 16 Oct 2003 03:27:33 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by jongert.nl (Postfix) with SMTP
	id E45023C83A1; Thu, 16 Oct 2003 08:27:31 +0200 (CEST)
X-AV-Checked: Thu Oct 16 08:27:32 2003 jongert.nl
Received: from IT001 (IT001.int.jongert.nl [10.0.1.253])
	by jongert.nl (Postfix) with SMTP id A234C3C83A0
	for <pgsql-sql@postgresql.org>; Thu, 16 Oct 2003 08:27:31 +0200 (CEST)
From: "R. van Twisk" <r.vantwisk@jongert.nl>
To: <pgsql-sql@postgresql.org>
Subject: Re: Sorting problem
Date: Thu, 16 Oct 2003 08:25:26 +0200
Message-ID: <001101c393ae$4a00b9e0$fd01000a@IT001>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2911.0)
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
In-Reply-To: <3F8D6B03.83A186EE@nsd.ca>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/189
X-Sequence-Number: 15589

I think what you actually want is natural sorting.

Ries

> -----Oorspronkelijk bericht-----
> Van: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]Namens Jean-Luc Lachance
> Verzonden: woensdag 15 oktober 2003 17:43
> Aan: George A.J
> CC: pgsql-sql@postgresql.org
> Onderwerp: Re: [SQL] 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
> 
> "George A.J" wrote:
> > 
> > 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
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
>