• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sorting Query problem in MySQL

 
Rajkumar balakrishnan
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 317
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes its Varchar.. But i dont want it to be a number as they may changed like RM001,R875 and so on.
 
Sridhar Santhanakrishnan
Ranch Hand
Posts: 317
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
cast your column as an unsigned integer in your ORDER BY clause

cast(rma_no as UNSIGNED)
 
Sridhar Santhanakrishnan
Ranch Hand
Posts: 317
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul,

Won't the cast fail if the column contains AlphaNumeric values?
 
Rajkumar balakrishnan
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic