Problems with "order by char field"...

Hi all.
I have another one problem. I have select. It looks like:

SELECT tableA.fieldA,
tableB.FieldA,
tableC.FieldA
FROM tableA
LEFT JOIN tableB ON Some conditions
LEFT JOIN tableC ON Some conditions
ORDER BY tableA.fieldA

A very simple example. But tableA.fieldA is a character field (CHAR (10)), and it contains numeric and also alpha characters. Like: "32ad". I want to sort this select by this field, but this way. For examle, tableA.fieldA contains this values: "1","23","327","185,""15","256","99","a89","c52","a77"

After sorting I want this: "1","15","23","99","256","327","a77","a89","c52"

But I get only this: "1","15","185","23","256","327","99","a77","a89","c52"

I tried this,

SELECT LPAD(RTRIM(tableA.fieldA),10,' ') AS Alias,
tableB.FieldA,
tableC.FieldA
FROM tableA
LEFT JOIN tableB ON Some conditions
LEFT JOIN tableC ON Some conditions
ORDER BY Alias

but it was also not good.

Can you help me with this problem? Thank you.

It is correct behaviour. You cannot sort strings as integers...
Engine know how to sort numbers or how to sort strings. You cannot mix that.
Actually, you can do what you ask, but with some more complex operations in stored procedure.
There is no simple answer for this...

--
Best regards,
Fikret Hasovic http://fikret.fbtalk.net
USAID TAMP Senior Programmer
* FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation
* JEDI VCS contributor
http://jedivcs.sourceforge.net/
* Firebird and Fyracle news
http://www.fyracle.org