X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 74AF9D1B26D for ; Tue, 13 Jul 2004 07:59:41 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 58467-09 for ; Tue, 13 Jul 2004 10:59:41 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id A0232D1B199 for ; Tue, 13 Jul 2004 07:59:32 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i6DAxbi7029525 for ; Tue, 13 Jul 2004 10:59:37 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i6DAthDn029148 for pgsql-sql@postgresql.org; Tue, 13 Jul 2004 10:55:43 GMT From: Stu Subject: Re: Sorting problem X-Newsgroups: comp.databases.postgresql.sql Date: Tue, 13 Jul 2004 06:54:44 -0400 References: <3610e3e1.0407120027.2e04f948@posting.google.com> User-Agent: KNode/0.7.7 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7Bit Message-ID: Lines: 49 X-Complaints-To: abuse@comcast.net X-DMCA-Complaints-To: dmca@comcast.net X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.1 To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/105 X-Sequence-Number: 18357 Pop text in front of that first value and it works: template1=# select text '##10############--------####79909999' template1=# as "sortfield" template1-# union template1-# select '###1############--------####79909999' template1-# order by sortfield; sortfield -------------------------------------- ###1############--------####79909999 ##10############--------####79909999 (2 rows) Stu Ruggero wrote: > Hi all, > I have a problem sorting varchar fields. > I will explain the problem with a simple example: > > this query > select '##10' as sortfield > union > select '###1' as sortfield > order by sortfield > produces this correct output: > '###1' > '##10' > > but this one > > select '##10############--------####79909999' as sortfield > union > select '###1############--------####79909999' as sortfield > order by sortfield > produces this wrong output: > '##10############--------####79909999' > '###1############--------####79909999' > > I'm using postgresql 7.4.3 on Suse Linux 9.1. > I created my db using: initdb --pgdata=../data/ --encoding=LATIN1 > --lc-collate=it_IT --lc-ctype=it_IT > > Any suggestion? > Tia > Ruggero