Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Order by with Varchar datatype

 
mark hunt
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic