0

I've problem to order the field sID_contents_p set to VARCHAR (255) form the table ttt stored an a database MySql version 8.0.17

This is the result on db-fiddle.com, which offers MySQL 8

I need this return, for example when sID_contents_p contains 1

+----------------+-----+
| sID_contents_p | sID |
+----------------+-----+
| 1.1            |   1 |
| 1.2            |   2 |
| 1.3            |   3 |
| 1.4            |   4 |
| 1.5            |   5 |
| 1.6            |   6 |
| 1.7            |   7 |
| 1.8            |   8 |
| 1.9            |   9 |
| 1.10           |  10 |
| 1.11           |  11 |
| 1.12           |  12 |
| 1.13           |  13 |
| 1.14           |  14 |
| 1.15           |  15 |
| 1.16           |  16 |
| 1.17           |  17 |
| 1.18           |  18 |
| 1.19           |  19 |
| 1.20           |  89 |
+----------------+-----+

I've tried this query but the return not what you want (see above)....

mysql> SELECT
    sID_contents_p,
    sID
FROM
    `ttt` WHERE SUBSTRING_INDEX(sID_contents_p,".",1) = 1
ORDER BY
    sID_contents_p ASC;
+----------------+-----+
| sID_contents_p | sID |
+----------------+-----+
| 1.1            |   1 |
| 1.10           |  10 |
| 1.11           |  11 |
| 1.12           |  12 |
| 1.13           |  13 |
| 1.14           |  14 |
| 1.15           |  15 |
| 1.16           |  16 |
| 1.17           |  17 |
| 1.18           |  18 |
| 1.19           |  19 |
| 1.2            |   2 |
| 1.20           |  89 |
| 1.3            |   3 |
| 1.4            |   4 |
| 1.5            |   5 |
| 1.6            |   6 |
| 1.7            |   7 |
| 1.8            |   8 |
| 1.9            |   9 |
+----------------+-----+
20 rows in set (0.03 sec)
Edward Sheriff Curtis
  • 487
  • 4
  • 17
  • 1
    That column is sorting exactly as it should, because you've inappropriately used a string to hold a numeric value. – Ken White Apr 16 '21 at 16:05

1 Answers1

2

The issue is the ordering. You have a string. One method is to order by the length first:

ORDER BY LENGTH(sID_contents_p), sID_contents_p

EDIT:

If the first component can also vary in length, then split the values and convert to ints:

ORDER BY SUBSTRING_INDEX(sID_contents_p, '.', 1) + 0,
         SUBSTRING_INDEX(sID_contents_p, '.', -1) + 0
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Note that if the goal is sort by the first number followed by the second, then this approach may not work, [see here](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7410026d34037a216d2b8b568d7e8563). – Tim Biegeleisen Apr 18 '21 at 10:44
  • @TimBiegeleisen . . . That is a good point. I added an alternative. – Gordon Linoff Apr 18 '21 at 11:02
  • LOL [so did I](https://stackoverflow.com/questions/67147322/mysql-sorting-column-set-as-varchar255/67147382#67147382) ... it seems the OP reasked today haha. – Tim Biegeleisen Apr 18 '21 at 11:06