File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Order by with Varchar datatype Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Order by with Varchar datatype" Watch "Order by with Varchar datatype" New topic

Order by with Varchar datatype

mark hunt

Joined: May 02, 2007
Posts: 8
I'm using Sybase. I have a table A that contains VendorNum, InvoiceNum as varchar datatype. If i do a select on the table and order it by VendorNum, InvoiceNum, it's not giving me expected results. It's returning the followin:

But if i convert it in int , it gives me expected results. meaning invoiceNUm 2, 3, 12, 14. But i can't convert it in int because the data might convert alphbets, - etc. Could someone please shed some light on doing order by with varchar datatype and getting expected results. This is what i was expecting to recieve.

Thanks for your time.

[ June 22, 2007: Message edited by: mark hunt ]
Chad Clites
Ranch Hand

Joined: Aug 16, 2005
Posts: 134
You are getting exactly the results I would expect when ordering by varchar. One trick that might work is (for example)
SELECT VendorNum, InvoiceNum FROM someTable ORDER BY VendorNum, HEX(InvoiceNum);

I don't know if Sybase supports it, or if it works with multiple conditions, but worth a shot.
Jan Cumps

Joined: Dec 20, 2006
Posts: 2565

Leftpadding with blanks also works to get propper sorting. Say that your maximum number of digits is 10,
than you can order by lpad(yourfield, 10)

Regards, Jan

OCUP UML fundamental and ITIL foundation
I agree. Here's the link:
subject: Order by with Varchar datatype
It's not a secret anymore!