Posted by Rik on 10/24/06 22:36

Bob Stearns wrote:
> DarthDubroc wrote:
>> Hi My Name is Danny,
>>
>> I have a problem with the Order By Clause.
>>
>> My scenario is that I have Varible Data inside a Table ie: A123,
>> B123, 1, 2 , 10, 11,13, 33
>> (this is just Pseudo)
>> Well when I do an Order By Clause it checks the numbers and The
>> Alphabets. The Alphabets with the numbers Work accordingly but the
>> numbers are different. It checks the Numbers ie: 1, 10, 11, 13, 2,
>> 33. Do you think that you can help me out with this Situation. I
>> think I have to do some sort of Advance Order By Clause. The only
>> problem is that I do not know how. Another solution is the Case
>> Function. I just started working with SQL and I'm very Intermediate
>> with this Program. If you can help Thx in advance, If not thx for
>> reading this email and giving me some of your needed time.
>>
>> Thank you ,
>>
> No, you need to put numeric data in numeric columns not CHARACTER (or
> VARCHAR or similar) columns. There is no general way to sort a mixed
> numeric and alphabetic data set so the numbers are properly sorted.
> Leading zeroes (or spaces) so that everything lines up will help: 01,
> 02, 10, 11, 13, 33. But, more generally, does AA11BB come before or
> after AA2BB?

It is indeed very unwise to have data like this.
You could cast everything to CHAR, and LPAD them with 0 untill a certain
amount of characters, but it will still be messy.
--
Grtz,

Rik Wasmus

[Back to original message]