CODEWITHSUNDEEP
mysqlmariadb
sfgroups
sfgroups

Reputation: 19109

MariaDB sort string value with faction

I have Linux kernel version like below, want to sort this value from kernel version oldest to latest.

4.18.0-348.7.1.el8_5.x86_64
4.18.0-358.el8.x86_64
4.18.0-305.19.1.el8_4.x86_64
4.18.0-348.12.2.el8_5.x86_64
4.18.0-348.7.1.el8_5.x86_64

Using this query, its not sorting by kernel version. I expect this line 4.18.0-348.12.2.el8_5.x86_64 should come above last line.

select kernel, REGEXP_REPLACE(kernel,'\\.|-|el.*$','') as k from os  group by kernel order by 1;

4.18.0-305.19.1.el8_4.x86_64,4180305191
4.18.0-338.el8.x86_64,4180338
4.18.0-348.12.2.el8_5.x86_64,4180348122
4.18.0-348.2.1.el8_5.x86_64,418034821
4.18.0-348.7.1.el8_5.x86_64,418034871
4.18.0-358.el8.x86_64,4180358

How to make this sql query sort by kernel version?

Upvotes: 0

Views: 100

Answers (2)

FanoFN
FanoFN

Reputation: 7114

How about like this:

SELECT kernel,
       k1, k2, k3,
       CASE WHEN k3 LIKE '%-%' THEN 0
        ELSE SUBSTRING_INDEX(k3,'.',1)+0 END AS k4,
       CASE WHEN k3 LIKE '%-%' THEN 0
        ELSE SUBSTRING_INDEX(k3,'.',-1) END AS k5
FROM       
(SELECT kernel,
       SUBSTRING_INDEX(kernel,'-',1) AS k1,
       SUBSTRING_INDEX(SUBSTRING_INDEX(kernel,'-',-1),'.',1) AS k2,
       SUBSTRING_INDEX(SUBSTRING_INDEX(kernel,'.el',1),'.',-2) AS k3
 FROM
    os) V
ORDER BY 
  k1, k2, k4, k5
;

With a bunch of SUBSTRING_INDEX() used starting from the subquery. The idea is to separate a few of the parts from the value where I see it's "orderable". The query above will return the following:

kernel k1 k2 k3 k4 k5
4.18.0-305.19.1.el8_4.x86_64 4.18.0 305 19.1 19 1
4.18.0-338.el8.x86_64 4.18.0 338 18.0-338 0 0
4.18.0-348.2.1.el8_5.x86_64 4.18.0 348 2.1 2 1
4.18.0-348.7.1.el8_5.x86_64 4.18.0 348 7.1 7 1
4.18.0-348.12.2.el8_5.x86_64 4.18.0 348 12.2 12 2
4.18.0-358.el8.x86_64 4.18.0 358 18.0-358 0 0

Demo

Upvotes: 1

danblack
danblack

Reputation: 14681

MariaDB in 10.7 had a successful trial of natural_sort_order, soon to be a GA release:

MariaDB [test]> create table v( version varchar(30));
Query OK, 0 rows affected (0.002 sec)

MariaDB [test]> insert into v values ('4.18.0-348.7.1.el8_5.x86_64'),('4.18.0-358.el8.x86_64'),('4.18.0-305.19.1.el8_4.x86_64'),('4.18.0-348.12.2.el8_5.x86_64'),('4.18.0-348.7.1.el8_5.x86_64');

MariaDB [test]> select version from v order by natural_sort_key(version);
+------------------------------+
| version                      |
+------------------------------+
| 4.18.0-305.19.1.el8_4.x86_64 |
| 4.18.0-348.7.1.el8_5.x86_64  |
| 4.18.0-348.7.1.el8_5.x86_64  |
| 4.18.0-348.12.2.el8_5.x86_64 |
| 4.18.0-358.el8.x86_64        |
+------------------------------+
5 rows in set (0.001 sec)

ref: has natural_sort_order

Upvotes: 2

Related Questions