Sorting Query problem in MySQL

[Rajkumar balakrishnan]

Hi all,
I developed a Web Application and successfully launched it too. But a day after, the client comes up with a problem.In my application, i use a button to generate number(A number used for reference in my project) and i checked it from two digits numbers. But when they gave 9 and after that the value doesnt get incremented from 10.

The Query i used was

SELECT no FROM tbl ORDER BY no ASC;

When i use the query in MySQL Command Line,the value 9 comes at last where the values bigger than this will come at first. Like

10
122
123
124
9

Why it happens?
I hope that the sorting wasn't working properly.

Is there any way to fix it?

[Sridhar Santhanakrishnan]

Can you check the datatype of the column in the DB? I think it is Varchar (or its MySQL equivalent) instead of Number.

hope that the sorting wasn't working properly

The sorting is proper if the type is String.

[Rajkumar balakrishnan]

Yes its Varchar.. But i dont want it to be a number as they may changed like RM001,R875 and so on.

[Sridhar Santhanakrishnan]

As I said before, the sorting is fine for a Varchar column.

How do you want the sorting to be done if the column values are a mix of both numeric and alpha-numberic values?

For example,

RM001
9
R875
8
RM005

[Paul Campbell]

cast your column as an unsigned integer in your ORDER BY clause

cast(rma_no as UNSIGNED)

[Sridhar Santhanakrishnan]

Paul,

Won't the cast fail if the column contains AlphaNumeric values?

[Rajkumar balakrishnan]

I fix the problem by adding a 0 at the end of query like this

SELECT no FROM tbl ORDER BY no+0 ASC;

Now its fixed.

Although i also solve the RM001 issue by splitting the 2digits from the var if it isn't get incremented and convert it to display the new number.