Suppose I have some data in my MySQL database such as:
01, 001, 2, 9, 009, 23, 15, 19, 09, 08, 1 etc. So, I want to sort the data ascending like:
001, 01, 1, 1, 2, 08, 009, 09, 9, 15, 19, 23 etc. If I use ORDER BY value ASC, It doesn't work. How should I be sorting it? I want the first value to be 1, then 2, 3 ..... Is that possible?
Since they are stored as string, you can simply multiply it by one as mysql automatically parses the value to a number. If it happens that the string starts with a letter, the value will be equal to zero.
ORDER BY col * 1, col As you can see, the order by clause does not have ASC keyword because by default, the optimizer sorts it in ascending order.
What you've written should work with integers. From your question though, I'm assuming that the numbers you're specifying are being stored as strings, so they're being sorted as string.
In that case, you need to cast them to integers for the sorting to work properly. That's as easy as doing something like
ORDER BY <column> / 1 ASC, <column> Multiplying or dividing by 1 causes the database to convert the string to an integer before sorting. The second use of means that 001 will always come before 1.
Re-reading your question, it sounds like you only are expecting to see 1 once, even if the table includes both 1 and 001. In that case do something like this:
SELECT DISTINCT (<column> / 1) AS intcol FROM ... ORDER BY intcol 
