RE: Order by - problem with numerics in varchar field

[Jay Blanchard]

[snip]
I'm having trouble with ordering.  I've got data in a varchar field that
currently gets ordered like this when I use 'order by myfield asc':

aristo 1001
aristo 156
aristo 222

I'd like it to order like this:

aristo 156
aristo 222
aristo 1001

How can I do this in MySQL?  Is there a way to take the numbers into account
when using order by?
[/snip]

SELECT columnName, RIGHT(columnName, 4) AS theNumber
FROM tableName
ORDER BY theNumber

+-+---+
| columnName  | theNumber |
+-+---+
| aristo 156  |  156  |
| aristo 222  |  222  |
| aristo 1001 | 1001  |
| aristo 2317 | 2317  |
+-+---+

Now, this will only work if you specify the correct number in the RIGHT()
function. Since you had 4 digit numbers, I used 4. But if this number is
longer, you need to increase your integer in the RIGHT() function as it will
not be able to determine the length of the number.

mysql select info AS columnName, SUBSTRING_INDEX(info,  , -1) AS
theNumber from tblStuff ORDER BY theNumber;
+-+---+
| columnName  | theNumber |
+-+---+
| aristo 1001 | 1001  |
| aristo 156  | 156   |
| aristo 222  | 222   |
| aristo 2317 | 2317  |
+-+---+

As you can see, the SUBSTRING_INDEX() function retrieves the number after
the space, but the query does not sort this the way that you want. RIGHT()
takes into account the space before the 3 digit integer and sorts numbers
with spaces first, which works for you in this case.

The problem with these solutions is that they may not work in every case
needed.


HTH!

Jay



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Order by - problem with numerics in varchar field

[Keith C. Ivey]

On 11 Jul 2002, at 16:06, Dan Lamb wrote:

 I'd like it to order like this:
 
 aristo 156
 aristo 222
 aristo 1001
 
 How can I do this in MySQL?  Is there a way to take the numbers into
 account when using order by?

There are various ways to break up your strings and convert part to a 
number using MySQL SQL, depending on what assumptions we're allowed 
to make about the format of your strings and the size of your 
numbers.  Jay Blanchard has posted one possibility.

However, your sorting will be much faster (and can use indexes 
better) if you don't have to do such calculations for each row every 
time you want to sort.  If you need to sort by a two-part key, then 
you really should split the key into two columns, make one VARCHAR 
and one SMALLINT (or whatever), and make an index on both.

-- 
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php