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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend