CODEWITHSUNDEEP
phpmysqlsorting
Daniel Adinugroho
Daniel Adinugroho

Reputation: 1399

Sort decimal in PHP or MySQL

I am developing a ledger application. My main problem is that my client has Chart of Account with code like this 

1.1.1, 
1.1.2 
...... 
1.1.10, 
1.1.11,
.........

Using PHP or MySQl I can only manage to sort them to 

1.1.1, 
1.1.10, 
1.1.11,
1.1.2, 
.......

Any help on how to sort it so that 1.1.10 is coming after 1.1.9?

Thanks in advance.

Upvotes: 3

Views: 1292

Answers (6)

Hariadi
Hariadi

Reputation: 606

SELECT col FROM mytable ORDER BY REPLACE(col, ".", "")+0

REPLACE remove . and MySQL automatically converts the string with a mathematical operation. You can try REPLACE(col, ".", "")*1

Upvotes: 2

Ed Gibbs
Ed Gibbs

Reputation: 26353

You need to pull out the digits between the decimals and treat them as numerics. Use SUBSTRING_INDEX to pull out the digits and CAST to turn them into numerics:

SELECT *
FROM myAccounts
ORDER BY
  CAST(SUBSTRING_INDEX(account_number, '.', 1) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(account_number, '.', -2) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(account_number, '.', -1) AS UNSIGNED)

Upvotes: 2

spencer7593
spencer7593

Reputation: 108450

This is ugly, but it will work:

ORDER
   BY SUBSTRING_INDEX(CONCAT( col ,'.'),'.',1) + 0
    , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',2),'.',-1) + 0
    , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',3),'.',-1) + 0
    , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',4),'.',-1) + 0
    , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',5),'.',-1) + 0
    , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',6),'.',-1) + 0
    , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',7),'.',-1) + 0

To test these expressions, you can use them in a SELECT and verify they extract the right components, and they are ordered correctly:

SELECT col
     , SUBSTRING_INDEX(CONCAT( col ,'.'),'.',1) + 0 AS p1
     , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',2),'.',-1) + 0 AS p2
     , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',3),'.',-1) + 0 AS p3
     , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',4),'.',-1) + 0 AS p4
     , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',5),'.',-1) + 0 AS p5
     , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',6),'.',-1) + 0 AS p6
     , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT( col ,'.'),'.',7),'.',-1) + 0 AS p7
  FROM mytable 
 ORDER BY 2,3,4,5,6,7,8

Rather than explain how this works, i'm just going to hit the important "tricks"

  • append a trailing "." on the end of the col, you need that so you don't get back the last position multiple times,

  • use SUBSTRING_INDEX to retrieve portion up to nth '.'

  • use SUBSTRING_INDEX to retrieve trailing portion of that (reading backwards, to the leading dot

  • add zero, to convert the string to a numeric value

Upvotes: 3

MISJHA
MISJHA

Reputation: 1008

I had a similar problem and I've managed it as the following:

SELECT .... , CAST(SUBSTRING([column_name],1) AS UNSIGNED) AS myNum,  CAST(SUBSTRING([column_name],3) AS UNSIGNED) AS myDec
FROM ...
WHERE ...
ORDER BY myNum, myDec

In your case maybe you will need an additional "depth". Hope this helps you to get some insight.

Upvotes: 0

Sutandiono
Sutandiono

Reputation: 1750

Assign the values to array and use natsort() to sort the values naturally.

$foo = array ('1.1.1', '1.1.2', '1.1.10', '1.1.11');
natsort ($foo);
print_r ($foo);

/*
Array
(
    [0] => 1.1.1
    [1] => 1.1.2
    [2] => 1.1.10
    [3] => 1.1.11
)
*/

?>

Upvotes: 2

DevZer0
DevZer0

Reputation: 13535

if the sorting field is name then use the following ORDER BY clause

ORDER BY length(name), name

Upvotes: 0

Related Questions