Postgres Pro
Downloads
Home   >   mailing lists

Thread: Sorting problem

Sorting problem

From
ruggero.maria.rossi@libero.it (Ruggero)
Date:
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
sortfieldorder 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

Re: Sorting problem

From
Stephan Szabo
Date:
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.

Re: Sorting problem

From
Stu
Date:
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