aspose file tools*
The moose likes JDBC 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
Bookmark "Order by with Varchar datatype" Watch "Order by with Varchar datatype" New topic
Author

Order by with Varchar datatype

mark hunt
Greenhorn

Joined: May 02, 2007
Posts: 8
Hi,
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.

Mark
[ 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
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

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
youtube channel
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Order by with Varchar datatype