Re: Sorting Varchar

[kc68]

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 <[EMAIL PROTECTED]> 
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: [EMAIL PROTECTED]
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 <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]>
>>
>> [EMAIL PROTECTED] 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/





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]