Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got a list of ledger account numbers. These numbers go like this:

  • 5 (main account, #5)
  • 501 (sub account of main account 5, #01)
  • 50101 (sub account of this account, #01)
  • etc.

So if I have a list and want to order this by account number, then I'd like to create this order:

  • 5
  • 501
  • 50101
  • 50102
  • 50103
  • 6
  • 601
  • 60101
  • 60102
  • 60103

etc.

However, any ORDER BY will give me:

  • 5
  • 6
  • 501
  • 601

etc.

So what is the proper way of working with this kind of numbering? I imagine I can get another column for ordering, but I really prefer to use the account numbers as IDs that I use for ordering, as not to make things too complicated.

share|improve this question
1  
Well, you could consider storing this data separately. What are you gaining by munging them together? If you store them separately, you can always order by a sum of the parts. –  Aaron Bertrand Jun 16 '12 at 20:46
    
What I gain is simplicity. I will be needing this account number very often, but only infrequently do I need to order the list. So storing this separately makes things more complicated for only one specific, uncommon use. –  user Jun 17 '12 at 14:56

1 Answer 1

up vote 4 down vote accepted

Convert it to CHAR first:

ORDER BY CAST( AccounNumber AS CHAR )
share|improve this answer
    
Superb, works like a charm! Never knew about this, thanks! –  user Jun 17 '12 at 14:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.