CODEWITHSUNDEEP
mysqldatabase
Jainesh shah
Jainesh shah

Reputation: 55

Sorting a "1.1.1" format type in Mysql

I want to Sort this type 1.1, 1.1.1, 1.1.2, 2.1, 2.1.1, 2.3,..etc. like this i am using Varchar datatype for this Column But what happens is it sort proper till 9.9.9 after this 10.1.1 here sorting happens like this 1.1, 1.1.1, 1.1.2, 10.1.1, 2.1, 2.1.1,...etc. like this I also tried using DECIMAL but no results.

Upvotes: 2

Views: 2036

Answers (3)

Cave Johnson
Cave Johnson

Reputation: 6778

Try this:

SELECT
    *
FROM
    my_table
ORDER BY 
    CAST(SUBSTRING_INDEX(my_col, '.', 1) AS UNSIGNED) ASC,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(my_col, '.', -2), '.', 1) AS UNSIGNED) ASC,
    CAST(SUBSTRING_INDEX(my_col, '.', -1) AS UNSIGNED) ASC

Basically it gets the substring of each part of the string, and sorts the rows by using each part of the string, going from left to right.

Update: The above query works for exactly 3 numbers. But if you want to support 4 numbers, then use this query:

SELECT
    *
FROM
    my_table
ORDER BY 
    SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(my_col, '.'), '.', 1), '.', -1) + 0
    , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(my_col, '.'), '.', 2), '.', -1) + 0
    , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(my_col, '.'), '.', 3), '.', -1) + 0
    , SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(my_col, '.'), '.', 4), '.', -1) + 0

And if you want to support more numbers, just add the following, replacing "XX" with the next number:

, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(my_col, '.'), '.', XX), '.', -1) + 0

Credit to this answer on a very similar question: https://stackoverflow.com/a/17849200/2518200

Upvotes: 0

François-Xavier Aeberhard
François-Xavier Aeberhard

Reputation: 632

A good idea would be to store your number as fixed size, e.g.

  • 1.1.1 => 001001001 (or in short 1001001)
  • 1.1 => 1001000
  • 9.9.9 => 9009009

This way you can store your variable as number. Upon retrieval, you will have to play with modulo to set your number back to string. The main limitation will be you will have to decide how many are available for each segment (in my example 999 is the max for each segment. This method is how I store IP adress i want to sort (e.g. 192.168.1.1 becomes 192168001001).

Upvotes: 0

hamilyon
hamilyon

Reputation: 387

You can parse value into three different columns, not nessesary in your database, application code will do it more easily. Then it becomes as simple as 

select ... order by col1, col2, col3

Upvotes: 1

Related Questions