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 40C6ED1B241
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 16 Jul 2004 16:14:47 -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 95774-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 16 Jul 2004 19:14:40 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 6B716D1B1BE
	for <pgsql-sql@postgresql.org>; Fri, 16 Jul 2004 16:14:37 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 6DB2635650; Fri, 16 Jul 2004 12:14:38 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 6C3D9355EF; Fri, 16 Jul 2004 12:14:38 -0700 (PDT)
Date: Fri, 16 Jul 2004 12:14:38 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Ruggero <ruggero.maria.rossi@libero.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sorting problem
In-Reply-To: <3610e3e1.0407120027.2e04f948@posting.google.com>
Message-ID: <20040716120950.J75128@megazone.bigpanda.com>
References: <3610e3e1.0407120027.2e04f948@posting.google.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
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/102
X-Sequence-Number: 18354

On Mon, 12 Jul 2004, 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?

it_IT looks like it's sorting based on the strings with the symbols
removed.  I get similar behavior on my system using the unix sort command
with it_IT.  This looks like it's a mismatch between what you want and the
collation you've provided.