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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


However, any ORDER BY will give me:

  • 5
  • 6
  • 501
  • 601


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
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
up vote 4 down vote accepted

Convert it to CHAR first:

share|improve this answer
Superb, works like a charm! Never knew about this, thanks! – user Jun 17 '12 at 14:55

Your Answer


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.