...

View Full Version : get results in alphanumric order, but letters first then numbers and other char-



needsomehelp
10-14-2011, 04:57 PM
How would I get all mysql results returned to be in order by letter then numbers after and then the other characters.

i.e.

a
b
c
d
...
0 (zero)
1
2
3
4


the following code i was given shows the results like so...


SELECT * FROM table_name ORDER BY LEFT(col_name,1) > 0, left(col_name,1), substring(col_name,2) > 0

0 (zero)
a
b
c
d
...
1
2
3
4

and the zero is not in the right order...

how do i get this to work right ?

BubikolRamios
10-14-2011, 07:11 PM
Don't ask for explanation why it works, was just playing around with it.



select f, Concat(f,1)*1
from table_name
order by Concat(f,1)*1,f

Old Pedant
10-14-2011, 08:10 PM
That doesn't work, at all, Bubikol!



mysql> select f, concat(f,1)*1 from f order by concat(f,1)*1,f;
+------+---------------+
| f | concat(f,1)*1 |
+------+---------------+
| 0a | 0 |
| a0 | 0 |
| abcd | 0 |
| 02x | 2 |
| 3A | 3 |
| 012 | 121 |
+------+---------------+
6 rows in set (0.00 sec)

The reason it *seems* to work: If you multiply a non-number by 1, MySQL converts the non-number to zero before the multiply. So that means that any data that starts with anything except a digit is treated the same as any data starting with a zero.

Old Pedant
10-14-2011, 08:20 PM
needssomehelp: We *CAN* play with the LEFT(field,1) to get the data in the right order so far as the FIRST CHARACTER is concerned.

Example:


mysql> select f from f order by LEFT(f,1) < 'A', left(f,1), substring(f,2);
+------+
| f |
+------+
| a0 |
| abcd |
| 012 |
| 02x |
| 0a |
| 3A |
+------+
6 rows in set (0.00 sec)

But as you can see, then the SECOND character ordering is wrong.

To do this for an arbitrary number of characters would take an ORDER BY expression that would be a mile long and still would fail if you added one more character to a the field that you hadn't accounted for.

If you need this ordering (letters before digits) to be effective for all characters in the field, you will have to create a custom collation order.

Look here, for starters:
http://download.oracle.com/docs/cd/E17952_01/refman-5.0-en/adding-collation.html

needsomehelp
10-14-2011, 08:31 PM
Just thinking about it what about if all entries were got in ascii order

SELECT * FROM `products` ORDER BY `productTitle` ASC

then have the entries that start with a letter extracted out and placed in a new array then have the entries that start with a number added to the end of the new array, as the query has already done an 'order by' only the extract and paste in to a new array would be needed?

EDIT: but still use the array string as a normal mysql results array string for the rest of page to process.

is this a better idea ?

Old Pedant
10-14-2011, 08:39 PM
have the entries that start with a letter extracted out and placed in a new array then have the entries that start with a number added to the end of the new array
And how does that solve the problem of getting the *SECOND* character in the field in your non-ASCII order???

How does that help get (for the simplest example)

aa
a0
0a
00
into that order?
Yes, that would put the "a" ahead of the "0" for the first character, but then the second characters would give you the natural ordering to produce


a0
aa
00
0a

If that's adequate, then it's trivial. Without your complex scheme.



mysql> select f from f order by LEFT(f,1) < 'A', f;
+------+
| f |
+------+
| a0 |
| abcd |
| 012 |
| 02x |
| 0a |
| 3A |
+------+
6 rows in set (0.00 sec)


But if you want *ALL* characters to be sorted in the order you asked for, there is NO good solution other than creating a custom collation order.

Did you look at the docs I pointed you to?

Did you look at this section:
http://download.oracle.com/docs/cd/E17952_01/refman-5.0-en/adding-collation-simple-8bit.html

That's really a simple process, and it will give much better performance and solve the problem of making sure that that ordering is correct for *ALL* characters in the field.

needsomehelp
10-14-2011, 08:52 PM
mysql> select f from f order by LEFT(f,1) < 'A', f;
+------+
| f |
+------+
| a0 |
| abcd |
| 012 |
| 02x |
| 0a |
| 3A |
+------+
6 rows in set (0.00 sec)



This done the trick, ok not perfect but it got the index to show in the right order, part from the numbers coming before in each of these list this I can live with.

Thank you for your help on this.

Old Pedant
10-14-2011, 08:59 PM
LOL! Teach me to try for perfection. <grin/> If that's adequate, go for it!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum