Monthly Archives: December 2015

Natural Sorting in MySQL

The data:

select id, name from Object where name like "dbrferrari%";
+-----+--------------+
| id  | name         |
+-----+--------------+
|   8 | dbrferrari5  |
|   9 | dbrferrari6  |
|  25 | dbrferrari1  |
|  26 | dbrferrari2  |
|  35 | dbrferrari3  |
|  64 | dbrferrari4  |
|  80 | dbrferrari7  |
|  99 | dbrferrari11 |
| 101 | dbrferrari8  |
| 102 | dbrferrari10 |
| 133 | dbrferrari12 |
| 134 | dbrferrari15 |
| 135 | dbrferrari14 |
| 199 | dbrferrari9  |
| 200 | dbrferrari16 |
| 202 | dbrferrari18 |
| 211 | dbrferrari13 |
+-----+--------------+

The problem:

select id, name from Object where name like "dbrferrari%" order by name asc;
+-----+--------------+
| id  | name         |
+-----+--------------+
|  25 | dbrferrari1  |
| 102 | dbrferrari10 |
|  99 | dbrferrari11 |
| 133 | dbrferrari12 |
| 211 | dbrferrari13 |
| 135 | dbrferrari14 |
| 134 | dbrferrari15 |
| 200 | dbrferrari16 |
| 202 | dbrferrari18 |
|  26 | dbrferrari2  |
|  35 | dbrferrari3  |
|  64 | dbrferrari4  |
|   8 | dbrferrari5  |
|   9 | dbrferrari6  |
|  80 | dbrferrari7  |
| 101 | dbrferrari8  |
| 199 | dbrferrari9  |
+-----+--------------+

The solution:

select id, name from Object where name like "dbrferrari%" order by LENGTH(name), name asc;
+-----+--------------+
| id  | name         |
+-----+--------------+
|  25 | dbrferrari1  |
|  26 | dbrferrari2  |
|  35 | dbrferrari3  |
|  64 | dbrferrari4  |
|   8 | dbrferrari5  |
|   9 | dbrferrari6  |
|  80 | dbrferrari7  |
| 101 | dbrferrari8  |
| 199 | dbrferrari9  |
| 102 | dbrferrari10 |
|  99 | dbrferrari11 |
| 133 | dbrferrari12 |
| 211 | dbrferrari13 |
| 135 | dbrferrari14 |
| 134 | dbrferrari15 |
| 200 | dbrferrari16 |
| 202 | dbrferrari18 |
+-----+--------------+