p3ace p3ace - 5 months ago 19
SQL Question

ORDER BY ASC not taking effect properly on String containing number

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();


enter image description here

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.

Answer

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 coulum

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 field

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]>