Search code examples
mysqlsortinggroup-bysql-order-by

MYSQL advanced sorting

I have a table full of id's (tID) which are created with a "w" in the beginning followed by a number (int) a dot and another number (int).

Sample id's: 

w1.3, w1.12, w1.1, w1.1, w2.10, w2.4

When I use ORDER BY tID the returned array is 

w1.1, w1.12, w1.2, w1.3, w2.10, w2.4.

I would like it to be 

w1.1, w1.2, w1.3, w1.12, w2.4, w2.10

Is this possible within a MYSQL query?

Solution

  • Yes, it's possible. But you should consider storing only the 2 integers, without the w and in 2 columns.

    In the meantime, you can use this:

    ORDER BY
    CAST(SUBSTRING_INDEX(REPLACE(tID, 'w', ''), '.', 1) AS SIGNED INT)
  , CAST(SUBSTRING_INDEX(tID, '.', -1) AS SIGNED INT)