ORDER BY ASC not taking effect properly on String containing number

Question

I'm using MySQL Workbench as IDE for creating my tables. I have created a stored procedure to show all rows of level column and attempted to order the result set in ascending order. level column is a varchar column.

CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllYearLevels`()
BEGIN
    SELECT `level` FROM yearlevel ORDER BY `level` ASC;
END

But when I perform a CALL to the getAllYearLevels procedure, it return the result with Grade 10 as second row instead of Grade 2.

call enrollmentdb.getAllYearLevels();

I know this is very simple to most but I just have no idea how to correct this. I used the correct syntax for ordering the result set. I thought maybe casting the varchar value is needed but I don't know how to break it apart. Or maybe, casting isn't necessary.

I'd appreciate any help.

Thanks.

the result is correct. is alphabetic . you can test **ORDER BY SUBSTRING_INDEX(level,' ',-1)+0 ASC** to get your wanted result — Bernd Buffen, Jul 07 '16 at 10:19
@Bernd Buffen Thanks a lot. I haven't used the SUBSTRING_INDEX(x,x,n)+0 before but this should add to my knowledge. I'll research more on this. I appreciate it. This solved my problem. — heisenberg, Jul 07 '16 at 10:27
a little remark: this is verytime a FULL TABLE SCAN. if you have huge table it is better to change the table and add a virtual perstant field which can automatic generate the number and you can also create an index on this field. inform me if you want more infos about that — Bernd Buffen, Jul 07 '16 at 10:32
Does the virtual field act like a primary key? The year levels I need to include is from Grade 1 - 12 only. Can you give a short example of implementing it. I googled it but I'm not sure if the example here https://mariadb.com/kb/en/mariadb/virtual-computed-columns/ applies. Thanks. — heisenberg, Jul 07 '16 at 10:42
Possible duplicate of [SQL-Order Strings containing numbers](http://stackoverflow.com/questions/35574965/sql-order-strings-containing-numbers) — Jocelyn, Jul 07 '16 at 10:47

score 1 · Answer 1 · answered Jul 07 '16 at 10:47

1

I suggest changing level to contain only numbers and adding the text "Grade" to the result. Then sorting on the new numeric column will give you the order you want.

answered Jul 07 '16 at 10:47

Jonny

1,037
7
15

Thanks. Yes I'll take note of the suggestion. – heisenberg Jul 07 '16 at 10:58

score 1 · Answer 2 · answered Jul 07 '16 at 10:50

1

Given your data, probably the easiest way is to sort by length first and then by the value:

ORDER BY LENGTH(`level`), `level

answered Jul 07 '16 at 10:50

Gordon Linoff

1,242,037
58
646
786

Thanks for the answer. – heisenberg Jul 07 '16 at 10:59

score 1 · Accepted Answer · edited Sep 15 '16 at 13:52

Here a little sample of use SUBSTRING_INDEX and VIRTUAL COLUMN

Sample: show sorted rows of a small table

MariaDB [yourschema]> SELECT * FROM l ORDER BY substring_index(LEVEL,' ',-1)+0;
+----+----------+
| id | level    |
+----+----------+
|  1 | Grade 1  |
|  3 | Grade 2  |
|  2 | Grade 10 |
+----+----------+
3 rows in set (0.04 sec)

Show table structure

MariaDB [yourschema]> show create table l;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                       |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| l     | CREATE TABLE `l` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `level` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Add a virtual persistent column:

MariaDB [yourschema]> ALTER TABLE l ADD level_int INT AS (SUBSTRING_INDEX(`level`,' ',-1)) PERSISTENT;
Query OK, 3 rows affected (1.90 sec)
Records: 3  Duplicates: 0  Warnings: 0

Add new rows:

MariaDB [yourschema]> insert into l (level) VALUES ('Grade 23'),('Grade 132');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Show all rows andd see the field level_int:

MariaDB [yourschema]> SELECT * FROM l ORDER BY substring_index(LEVEL,' ',-1)+0;
+----+-----------+-----------+
| id | level     | level_int |
+----+-----------+-----------+
|  1 | Grade 1   |         1 |
|  3 | Grade 2   |         2 |
|  2 | Grade 10  |        10 |
|  4 | Grade 23  |        23 |
|  5 | Grade 132 |       132 |
+----+-----------+-----------+
5 rows in set (0.00 sec)

Now you can add a index and query on the new column:

MariaDB [yourschema]> alter table l add index idx_level_int (level_int);
Query OK, 5 rows affected (0.93 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [yourschema]>

Thanks again. I appreciate the examples. It's very easy to follow and understand. This will help me in future projects because I really spend time with my procedures and table definition. :) — heisenberg, Jul 07 '16 at 10:57

score 1 · Answer 4 · answered Jul 09 '16 at 16:26

1

Schema design flaw... Don't store "Grade 1" in a string column, store "1" in a numeric column. When displaying, prepend with "Grade " if needed.

answered Jul 09 '16 at 16:26

Rick James

135,179
13
127
222

Thanks. I realized that storing number is better after seeing the suggestions. – heisenberg Jul 09 '16 at 16:29

ORDER BY ASC not taking effect properly on String containing number

4 Answers4