It's not a secret anymore!*
The moose likes JDBC and the fly likes Sorting Query problem in MySQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Sorting Query problem in MySQL" Watch "Sorting Query problem in MySQL" New topic
Author

Sorting Query problem in MySQL

Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

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?


Never try to be a hard-worker. Be a smart-worker.
My Blog
Sridhar Santhanakrishnan
Ranch Hand

Joined: Mar 20, 2007
Posts: 317
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

Joined: May 29, 2008
Posts: 445

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

Joined: Mar 20, 2007
Posts: 317
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

Joined: Oct 06, 2007
Posts: 338
cast your column as an unsigned integer in your ORDER BY clause

cast(rma_no as UNSIGNED)
Sridhar Santhanakrishnan
Ranch Hand

Joined: Mar 20, 2007
Posts: 317
Paul,

Won't the cast fail if the column contains AlphaNumeric values?
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

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.

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Sorting Query problem in MySQL