ORDER BY mysql numbers in correct order

Question

I have a list of data from SQL field. I want to sort by the field ASC but when i do it comes up in the wrong order. I know why it does it, but wondered if there was a solution around this problem. Ive heard of natsort php function, but not investigated it. Is there an easy way?

Academy

Under 10′s Blue

Under 10′s Green

Under 11′s Red

Under 11′s White

Under 13′s Blue

Under 13′s Red

Under 13′s White

Under 14′s Blue

Under 15′s Blue

Under 15′s Red

Under 15′s White

Under 16′s Red

Under 18′s Blue

Under 18′s Red

Under 7′s

Under 8′s Red

Under 9′s Red

possible duplicate of [Natural Sort in MySQL](http://stackoverflow.com/questions/153633/natural-sort-in-mysql) — nobody, Oct 11 '11 at 14:30

RolandoMySQLDBA · Accepted Answer · 2011-10-11T16:49:37.420

There is a very simple approach to sort this list. For all values of Academy starting with Under you perform the sort algorithm based on the following ORDER BY clause:

ORDER BY
REPLACE(Academy,'Under ','') + 0,Academy

The first sort column is based on removing the string 'Under ' and then adding 0. This will force an ordering of the resulting integer.

Here is an example of computing the numeric value by removing 'Under ' first:

mysql> select REPLACE('Under 15\'s Red','Under ','') + 0;
+--------------------------------------------+
| REPLACE('Under 15\'s Red','Under ','') + 0 |
+--------------------------------------------+
|                                         15 |
+--------------------------------------------+
1 row in set (0.00 sec)

The second sort column will order by the string value of Academy. All 'Under 15's' are grouped together and alphanumerically sorted.

Here is your sample data from the question loaded into a table and sorted:

mysql> use test
Database changed
mysql> drop table if exists under99color;
Query OK, 0 rows affected (0.01 sec)

mysql> create table under99color
    -> (academy varchar(30),
    -> id int not null auto_increment,
    -> primary key (id),
    -> index academy (academy)) engine=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> show create table under99color\G
*************************** 1. row ***************************
       Table: under99color
Create Table: CREATE TABLE `under99color` (
  `academy` varchar(30) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `academy` (`academy`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into under99color (academy) values
    -> ('Under 10\'s Blue'),('Under 10\'s Green'),('Under 11\'s Red'),
    -> ('Under 11\'s White'),('Under 13\'s Blue'),('Under 13\'s Red'),
    -> ('Under 13\'s White'),('Under 14\'s Blue'),('Under 15\'s Blue'),
    -> ('Under 15\'s Red'),('Under 15\'s White'),('Under 16\'s Red'),
    -> ('Under 18\'s Blue'),('Under 18\'s Red'),('Under 7\'s'),
    -> ('Under 8\'s Red'),('Under 9\`s Red');
Query OK, 17 rows affected (0.00 sec)
Records: 17  Duplicates: 0  Warnings: 0

mysql> select academy from under99color
    -> ORDER BY REPLACE(Academy,'Under ','') + 0,Academy;
+------------------+
| academy          |
+------------------+
| Under 7's        |
| Under 8's Red    |
| Under 9`s Red    |
| Under 10's Blue  |
| Under 10's Green |
| Under 11's Red   |
| Under 11's White |
| Under 13's Blue  |
| Under 13's Red   |
| Under 13's White |
| Under 14's Blue  |
| Under 15's Blue  |
| Under 15's Red   |
| Under 15's White |
| Under 16's Red   |
| Under 18's Blue  |
| Under 18's Red   |
+------------------+
17 rows in set (0.00 sec)

mysql>

Give it a Try !!!

Good offer without knowing the table structure and just going on the single column provided... Stripping the leading text to the point of expected number, and letting it come up with the numeric converted value of the results is a trick I've used MANY MANY MANY years ago too. — DRapp, Oct 11 '11 at 17:43
@DRapp - Some tricks never die off. However, this stunt may not fly with a huge table depending on any temp tables generated and the actual table structure. Another alternative would be to store the computed value in table and include that numeric column in the ORDER BY. Creating an index by the computed value + Acadmy column would speed up such an ORDER BY. — RolandoMySQLDBA, Oct 11 '11 at 17:49
I completely agree, but again, good answer based on what you were provided and know truly knowing the underlying structure of the table to work with. — DRapp, Oct 11 '11 at 19:33

Joel Etherton · Answer 2 · 2011-10-11T14:40:21.677

You can add a field to the selection query that uses a CAST to bring it into a numeric. First you'll have to come up with a substring method that will select the number from the string in the first place (perhaps use a Field function on the space and the '). Once you've got it isolated as an integer, sorting at that point should be trivial.

Possible example (pseudo-code - may not work "out of the box"):

SELECT TeamType, CAST(SUBSTRING(TeamType, FIELD(' ', TeamType), FIELD('\'', TeamType) - Field(' ', TeamType)), UNSIGNED) As TeamAge
FROM Teams
ORDER BY TeamAge, TeamType

score 0 · Answer 3 · answered Oct 11 '11 at 14:31

0

Your field is string! Thus it's sorting the string values.

answered Oct 11 '11 at 14:31

scriptmonster

2,741
21
29

score 0 · Answer 4 · answered Oct 11 '11 at 14:32

0

It has sorted it alphabetically; you need to parse the output field and sort them based on the number later.

answered Oct 11 '11 at 14:32

Jagat

1,392
2
15
25

score 0 · Answer 5 · answered Oct 11 '11 at 14:32

Well, you could store the number and the colour separately (with the option of a blank colour) and then order by number followed by colour. E.g:

SELECT CONCAT('Under ',ageIndex,'\'s ',colour) AS Team FROM Academy
ORDER BY ageIndex, colour

The possible advantage of this, depending on your requirements, is that you can then also run queries on ages and colours separately.

ORDER BY mysql numbers in correct order

5 Answers5