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/
