Mysql order by with decimal and character-CodePudding

I've a table with the below rows.

id	clause_no
1	5.8
2	5.9
3	5.1.1
4	5.10.2
5	5.1
6	5.10.2
7	5.4.3.2
8	5.40.3.2
9	5.6.3.2
10	5.40.3.3
11	K.8
12	L.26
13	K.3.2
14	Annex F

I'd like to display it in the below format:

id	clause_no
5	5.1
3	5.1.1
7	5.4.3.2
9	5.6.3.2
1	5.8
2	5.9
4	5.10.2
6	5.10.2
8	5.40.3.2
10	5.40.3.3
14	Annex F
13	K.3.2
11	K.8
12	L.26

I tried the order by, but it's not working as expected. PFB the db-fiddle link:

db-fiddle

CodePudding user response：

well, I think you have to split that clause_no column, which must be a string column (not a float...). Need to make sure they sort as numbers so '10' > '5'..

ORDER BY CAST(SUBSTRING_INDEX(CONCAT(clause_no,'.0'),'.',1) AS UNSIGNED), 
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(clause_no,'.0'),'.',2),'.',-1) AS UNSIGNED), 
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(clause_no,'.0'),'.',3),'.',-1) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(clause_no,'.0'),'.',4),'.',-1) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(clause_no,'.0'),'.',5),'.',-1) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(clause_no,'.0'),'.',6),'.',-1) AS UNSIGNED)

CodePudding user response：

You may "normalize" your clause_no values. The solution applicable to your In live, it is 5.7.23-23. version.

SELECT id, clause_no
FROM test
JOIN (SELECT 1 num UNION SELECT 2 UNION SELECT 3
      UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) numbers 
    ON LENGTH(clause_no) - LENGTH(REPLACE(clause_no, '.', '')) >= num - 1
GROUP BY id, clause_no
ORDER BY GROUP_CONCAT(LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(clause_no, '.', num), '.', -1), 8, 0) ORDER BY num SEPARATOR '');

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=d4ce86391c7bc8a630b79fd4673abdf9

The query assumes:

Max. amount of groups in clause_no is 6. May be increased by according subquery expand.
The value of a component contains not more than 8 digits. May be adjusted by changing according LPAD parameter.

There is one more possibility, I forget to add in the data. Updated the fiddle. Few Characters also will be there(eg: K.8), those characters need to display last after all the number sorting. – Hello

SELECT id, clause_no
FROM test
JOIN (SELECT 1 num UNION SELECT 2 UNION SELECT 3
      UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) numbers 
    ON LENGTH(clause_no) - LENGTH(REPLACE(clause_no, '.', '')) >= num - 1
GROUP BY id, clause_no
ORDER BY GROUP_CONCAT(CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(clause_no, '.', num), '.', -1) REGEXP '[^0-9]'
                           THEN RPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(clause_no, '.', num), '.', -1), 8, 0) 
                           ELSE LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(clause_no, '.', num), '.', -1), 8, 0) 
                           END ORDER BY num SEPARATOR '');

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=270e63d7823ed6076b033061e7da33ba

CodePudding user response：

You can try:

SELECT * FROM clause_no ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(clause_no,'.0.0.0'),'.',4))

but it'll work only up to 3 decimal points. This is because this isn't the intended purpose of INET_ATON function but it fits well here.