CODEWITHSUNDEEP
mysqlsortingcomparisonsql-order-by
hmak
hmak

Reputation: 3978

How to order data by custom algorithm in MySQL?

I have a hierarchical table with some data in it. I've handled sorting them in a hierarchical way but there is a bug in it. I use a field named Path to do this. let's look at the table first: enter image description here

The problem here is that mysql compares each pair of Paths as text. That if you look at those last two rows, 15 is bigger than 6 and should be swapped with the row under it.

My question is how can I compare each part of the path as numbers separated by dots?

Upvotes: 2

Views: 416

Answers (3)

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

Let's say you have sample data like this :

id    num_text
---------------
1     0.4.3
2     0.4.2
3     0.4.15
4     0.4.23

This is query you need :

select *
from table
order by concat(
    substr(num_text,1,1),
    substr(num_text,3,1),
    lpad(substr(num_text,5,2),2,'0')
    ) asc

sample output :

id    num_text
---------------
2     0.4.2    
1     0.4.3
3     0.4.15
4     0.4.23

Explanation :

First, you need to make each values have the same length. For example, we convert 0.4.2 to 0402, 0.4.3 to 0403, etc. So this query :

select id,
concat(
        substr(num_text,1,1),
        substr(num_text,3,1),
        lpad(substr(num_text,5,2),2,'0')
        ) as x
from table

the output should be :

id    x
--------------
1     0403
2     0402
3     0415
4     0423

The sample above is for the simple pattern. If you have more complicated or bigger pattern like 1.23.23.123 or 12.1.34.234, You need more mysql string function like LPAD, SUBSTR, etc.

Upvotes: 3

mynawaz
mynawaz

Reputation: 1594

What you can do is use two digits for each step of your path i.e. instead of 0.4.6 save 0.04.06 which is smaller than 0.04.15

What I'd do on top of that is convert the path to numbers using following formula

0.1.7.9.10.13 -> 0.1791013

multiply with 10000000

0.1791013 * 10000000 = 1791013

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

Here's a little hack:

SELECT INET_ATON('0.4.15')a,INET_ATON('0.4.6')b;
+--------+--------+
| a      | b      |
+--------+--------+
| 262159 | 262150 |
+--------+--------+

Upvotes: 0

Related Questions