I've just tried the three column solution (designating each column as
null), but the empty cells produce a "0" just as the two cells with an
actual "0." How do I avoid that, preferably with a blank in that place?
*****
On Thu, 13 May 2004 13:53:37 -0400, Bill Easton <bill1038@stripped>
wrote:
> OK, I'm sorry. The solution I gave doesn't work.
>
> You need to have some sort of conditional in the sort expression so that
> numbers are sorted numerically and other things are sorted
> alphanumerically.
> I'm not aware of a test for numeric vaues in MySql, so you need to use
> some
> trick to differentiate between the two.
>
> This seems to work, provided there are no negative numbers and the text
> things start with letters
>
> ORDER BY IF (cost < ':', LPAD(cost,10,'0'), CONCAT('1',cost))
>
> In this,
> cost < ':' tests if the string starts with a digit (':' is the
> character
> after '9')
> LPAD(cost,10,'0') pads the integer on the left with zeros -- replace
> the
> 10
> with a number at least one more than the maximum number of digits
> CONCAT('1',cost) causes the text items to sort to the end (the numbers
> now start with '0')
>
> Pasha's solution is probably cleaner, but you have to change the
> table definition. For Pasha's solution to work, you would need to
> have the text_val column be null (or '') when the value is numeric.
>
>
>> Subject: Re: Sorting Varchar
>> From: kc68@stripped
>> Date: Wed, 12 May 2004 18:13:17 -0400
>>
>> That didn't do it. I got 0,0,1050,1168,1195, 1975,
>> 150,155,16500,170,178.
>> . . The non-numerics came out last (which I want). There are two zeros
>> but no negative numbers. Any alternatives?
>>
>> Ken
>>
>> ******
>> On Wed, 12 May 2004 07:47:11 -0400, Bill Easton <bill_easton@stripped>
>> wrote:
>>
>> > You could also use "order by cost+0,cost". This puts the non-numerics
>> > first, then the numerics in numerical order. (You'd need to get
>> fancier
>> > if there are non-numerics starting with a digit or numerics <= 0.)
>> >
>> >> From: Sasha Pachev <sasha@stripped>
>> >>
>> >> kc68@stripped wrote:
>> >> > How do I set up a column (cost) that contains numbers and text so
> that
>> >> > the numbers will sort the numbers accurately? Using varchar
>> results
>> >> in
>> >> > a sort based on the first digit, so that I get e.g. 1, 10, 100, 3.
>
>> .
> .
>> >> > when the command is "order by cost." Almost all of the text is "By
>> > County."
>> >>
>> >> Ken:
>> >>
>> >> Consider having two columns - num_val, and text_val, and then order
>> by
>> > num_val,
>> >> text_val
>> >>
>> >> --
>> >> Sasha Pachev
>> >> Create online surveys at http://www.surveyz.com/
>
