CODEWITHSUNDEEP
mysql
Hello
Hello

Reputation: 41

Mysql order by with decimal and character

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

Upvotes: 0

Views: 262

Answers (3)

Akina
Akina

Reputation: 42622

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:

  1. Max. amount of groups in clause_no is 6. May be increased by according subquery expand.
  2. 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

Upvotes: 0

Shivam Agrawal
Shivam Agrawal

Reputation: 491

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.

Upvotes: 0

Geoduck
Geoduck

Reputation: 9005

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)

Upvotes: 1

Related Questions