Sorting problem

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

Re: Sorting problem

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